问题描述
我有一个datagridview，其中有六列。我使用左连接语句来组合4个表，以便我可以通过文件管理器筛选数据。我使用Individual_ID来组合表格。
如果所有表格组合只有一个对个人ID的引用，那么它会带来这些结果。但是，如果我为个人ID添加多个引用，则不会显示任何表数据，为什么会这样。
示例数据：
个人资料表
个人_ID
First_name
Middle_name
Last_name
Month_of_birth
Day_of_birth
Year_of_birth
POB
死亡桌子
Death_ID
DOD
POD
Cause_of_death
Individual_ID
公墓桌
Cem_ID
Cem_name
Place_of_burial
Individual_ID
我的datagridview设置如下：
Individual_ID名称DOB POB
1 Johnson，john 10/18/1900芝加哥，伊利诺伊州
2 Mathews，Mike 11/28/1910巴尔的摩，MD
我使用的查询是：
I have a datagridview where I have six columns. I am using a left join statement to combine 4 tables so I can sift through the data by a filer. I use the Individual_ID to combine the tables.
If all tables combined have only one reference to the individual ID it brings those results up. However, If I add more than one reference to the individual ID it brings up no table data, why is that.
Example data:
Profile table
Individual_ID
First_name
Middle_name
Last_name
Month_of_birth
Day_of_birth
Year_of_birth
POB
Death table
Death_ID
DOD
POD
Cause_of_death
Individual_ID
Cemetery table
Cem_ID
Cem_name
Place_of_burial
Individual_ID
my datagridview is set up like this:
Individual_ID Name DOB POB
1 Johnson, john 10/18/1900 Chicago, Illinois
2 Mathews, Mike 11/28/1910 Baltimore, MD
The query I am using is:
"Select profile.Individual_ID, First_name, Middle_name, Last_name, Sex, Race, Place_of_birth, County_of_birth, State_of_birth, Place_of_death, State_of_death, County_of_death, Month_of_death, day_of_death, Year_of_death, Cause_of_death, Cemetery_name, Cemetery_state, Cemetery_county, Cemetery_address, Section, Row, Lot, Grave, Burial_date From profile left join death On Profile.Individual_ID = death.Individual_ID left join cemeteries On death.cemetery_ID = cemeteries.cemetery_ID left join cemetery_reference On Cemetery_reference.cemetery_ID = cemeteries.cemetery_ID "
让我们说一个人被埋了2次然后上面的查询不起作用。但是，如果一个人被埋葬一次，那么一切都会出现在gridview中。只有当Individual_ID从其他表中多次引用时才会出现问题。
我尝试了什么：
尝试在所有连接语句中轮换，但如果我有多个Individual_ID的引用，则仍然无法显示任何数据。
let's say a person was buried 2 times then the above query does not work. However, if a person is buried once then everything shows up in gridview. It's only when the Individual_ID if referenced from the other tables more than once that gives me an issue.
What I have tried:
Tried to rotate through all the join statements, but still could not get any data to appear if I have more than one reference of an Individual_ID.
推荐答案
DECLARE @Person TABLE(PersonID INT IDENTITY(1,1), FName NVARCHAR(30), LName NVARCHAR(50), DOB DATETIME, POB NVARCHAR(150), DOD DATETIME, COD NVARCHAR(255))
--DOB -> Date Of Born
--DOD -> Date Of Death
--COD -> Cause Of Death
DECLARE @Cementary TABLE(CementaryID INT IDENTITY(1,1), CName NVARCHAR(30), City NVARCHAR(150))
DECLARE @PlaceOfBuried TABLE(PobID INT IDENTITY(1,1), PersonID INT, CementaryID INT, AlleyNo INT, PlaceNo INT, DOA DATETIME)
--DOA -> Date Of Burial
INSERT INTO @Person (FName, LName, DOB, POB, DOD, COD)
VALUES ('John', 'Doe', '1932-05-25', 'Alabama', '2002-06-01', 'Cancer'),
('Joe', 'Doe', '1940-01-15', 'Paris', '2001-12-21', 'Natural (age)'),
('Jimmy', 'Doe', '1938-02-12', 'Paris', '1997-11-11', 'Car accident')
INSERT INTO @Cementary (CName, City)
VALUES('PSG', 'Paris'), ('AHC', 'Alabama')
INSERT INTO @PlaceOfBuried (PersonID, CementaryID, AlleyNo, PlaceNo, DOA)
VALUES(1, 1, 1, 1, '2002-06-06'),
(2, 2, 1, 1, '2001-12-27'),
(1, 2, 2, 2, '2018-06-30'),
(2, 1, 3, 3, '2018-07-01')
SELECT P.*, C.CName, C.City, PB.AlleyNo, PB.PlaceNo, PB.DOA
FROM @PlaceOfBuried AS PB
INNER JOIN @Person AS P ON P.PersonID = PB.PersonID
INNER JOIN @Cementary AS C ON C.CementaryID = PB.CementaryID
ORDER BY P.PersonID, PB.DOA
上面的结果 SELECT 声明：
Result of above SELECT statement:
PersonID FName LName DOB POB DOD COD CName City AlleyNo PlaceNo DOA
1 John Doe 1932-05-25 00:00:00.000 Alabama 2002-06-01 00:00:00.000 Cancer PSG Paris 1 1 2002-06-06 00:00:00.000
1 John Doe 1932-05-25 00:00:00.000 Alabama 2002-06-01 00:00:00.000 Cancer AHC Alabama 2 2 2018-06-30 00:00:00.000
2 Joe Doe 1940-01-15 00:00:00.000 Paris 2001-12-21 00:00:00.000 Natural (age) AHC Alabama 1 1 2001-12-27 00:00:00.000
2 Joe Doe 1940-01-15 00:00:00.000 Paris 2001-12-21 00:00:00.000 Natural (age) PSG Paris 3 3 2018-07-01 00:00:00.000
如你所见，我使用了 INNER JOIN ，因为我想要显示所有被埋的人。如果您想要显示所有人（埋没/未埋葬），您必须将连接类型更改为 LEFT | RIGHT JOIN 。
祝你好运！
As you see, i've used INNER JOIN, because i wanted to display all person who have been buried. In case, you wanted to dispaly all person (buried/not buried), you have to change type of join to LEFT|RIGHT JOIN.
Good luck!
这篇关于我如何展示一对多的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！