Click here to Skip to main content
14,920,432 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 "


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.
Posted
Updated 19-Sep-18 20:38pm
v3
Comments
Santosh kumar Pithani 19-Sep-18 4:32am
   
AS you notice "If I add more than one reference to the individual ID it brings up no table data"..I think you used "AND" operator to add another condition so use "OR" instead of that you can find better.
Maciej Los 19-Sep-18 6:47am
   
Can you share sample data and your query? Use "Improve question" widget.
Have you seen my answer to the question: How do I get a 4 table inner join to work (VB.NET)[^]?
Member 11856456 19-Sep-18 11:57am
   
Yeah, I had to change the statement to a left join because not everything was connected at the time and when you use an inner join it looks for exact matches. However, that did help me understand where I went wrong in writing my join statements.
phil.o 19-Sep-18 10:23am
   
Do you mean handling the case where a single individual can be dead several times, or burried in several places?
Anyway, the What I have tried: section is the place where you should show us your actual query, so that we can have a better view on your issue. Please use the green "Improve question" button and qualify it with relevant query or queries.
Member 11856456 19-Sep-18 12:37pm
   
I revised my question and included more plus I added my query.

Your database structure is wrong. I do NOT recommend to separate death details from person. This is strictly connected with person (in other words: a man can not die twice /or more/)
I'd strongly recommend to read about Database normalization[^].

Take a look at example database structure:

SQL
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 


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


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!
   
v2
Comments
Member 11856456 22-Sep-18 14:19pm
   
Sorry for the confusion. I have separated the deaths because everyone in my database not only people who have died, the database also includes living relatives. If the information only included dead individuals I would have designed one table to include that data also. I originally was using the inner join. However, if the information has only the date of death I get 0 results and it leaves me with a blank datagrid. Where you have personID I only want that name to show up 1 time and the rest will be handled on another form.
Use the ID as a secondary, composite or foreign key when referencing it in the other tables, depending on how they interact
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900