How do I show a one to many relationship

Question

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 18-Sep-18 20:14pm

Member 11856456

Updated 19-Sep-18 20:38pm

v3

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2018-09-19T20:39:00

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!

yanda22 · Answer 2 · 2018-09-19T03:36:00

Solution 1

Use the ID as a secondary, composite or foreign key when referencing it in the other tables, depending on how they interact

Posted 19-Sep-18 3:36am

yanda22