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:
DECLARE @Person TABLE(PersonID INT IDENTITY(1,1), FName NVARCHAR(30), LName NVARCHAR(50), DOB DATETIME, POB NVARCHAR(150), DOD DATETIME, COD NVARCHAR(255))
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)
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!