morne morne - 5 months ago 15
SQL Question

Group By or Distinct in a multi table query

I have this query as follows, It returns the correct result, but I would like to use GROUP BY or DISTINCT to narrow the selection down on

lnk_ID
(3rd column)

I have tried them both, but it fails every time.

SELECT dmg.dmg_FirstName,
dmg.dmg_Surname,
lnk.lnk_ID,
dlk.dlk_AssessDate,
dmg.dmg_Sex,
trn.ScotHealthboard
FROM DAILY_LINK dlk
JOIN Absent ab ON ab.ScotRefID = dlk.dlk_ID
JOIN Link lnk ON lnk.lnk_ID = ab.Person_ID
JOIN Demographic dmg ON dmg.dmg_ID = lnk.lnk_dmgID
JOIN Training trn ON trn.Trn_ID = ab.Training_ID
WHERE dlk.dlk_AssessDate >= '2015/01/01' AND dlk.dlk_AssessDate <= '2015/12/01'
ORDER BY dmg.dmg_Surname, dmg.dmg_FirstName, lnk.lnk_ID;





Nuha Abdelwahab 566106181 16/07/2015 00:00:00 Tayside
Nuha Abdelwahab 566106181 16/07/2015 00:00:00 Tayside
Nuha Abdelwahab 566106181 16/07/2015 00:00:00 Tayside
Nuha Abdelwahab 566106181 16/07/2015 00:00:00 Tayside
Nuha Abdelwahab 566106181 16/07/2015 00:00:00 Tayside
Nuha Abdelwahab 566106181 16/07/2015 00:00:00 Tayside
Nuha Abdelwahab 566106181 16/07/2015 00:00:00 Tayside
Patricia Agnew 566106230 17/09/2015 00:00:00 Female Dumfries and Galloway
Patricia Agnew 566106230 17/09/2015 00:00:00 Female Dumfries and Galloway
Patricia Agnew 566106230 17/09/2015 00:00:00 Female Dumfries and Galloway
Patricia Agnew 566106230 17/09/2015 00:00:00 Female Dumfries and Galloway
Patricia Agnew 566106230 17/09/2015 00:00:00 Female Dumfries and Galloway
Patricia Agnew 566106230 17/09/2015 00:00:00 Female Dumfries and Galloway
Patricia Agnew 566106230 17/09/2015 00:00:00 Female Dumfries and Galloway
Patricia Agnew 566106230 17/09/2015 00:00:00 Female Dumfries and Galloway
Richard Airey 566105926 15/01/2015 00:00:00 Male Tayside
Richard Airey 566105926 15/01/2015 00:00:00 Male Tayside
Aileen Allan 566105477 25/09/2015 00:00:00 Female Forth Valley
Aileen Allan 566105477 25/09/2015 00:00:00 Female Forth Valley
Aileen Allan 566105477 25/09/2015 00:00:00 Female Forth Valley
Aileen Allan 566105477 25/09/2015 00:00:00 Female Forth Valley
Aileen Allan 566105477 25/09/2015 00:00:00 Female Forth Valley
Aileen Allan 566105477 25/09/2015 00:00:00 Female Forth Valley
Aileen Allan 566105477 25/09/2015 00:00:00 Female Forth Valley


EXPECTED RESULT

Nuha Abdelwahab 566106181 16/07/2015 00:00:00 Tayside
Patricia Agnew 566106230 17/09/2015 00:00:00 Female Dumfries and Galloway
Richard Airey 566105926 15/01/2015 00:00:00 Male Tayside
Aileen Allan 566105477 25/09/2015 00:00:00 Female Forth Valley


THE FOLLOWING FAILED

SELECT dmg.dmg_FirstName,
dmg.dmg_Surname,
DISTINCT (lnk.lnk_ID),
dlk.dlk_AssessDate,
dmg.dmg_Sex,
trn.ScotHealthboard
FROM DAILY_LINK dlk
JOIN Absent ab ON ab.ScotRefID = dlk.dlk_ID
JOIN Link lnk ON lnk.lnk_ID = ab.Person_ID
JOIN Demographic dmg ON dmg.dmg_ID = lnk.lnk_dmgID
JOIN Training trn ON trn.Trn_ID = ab.Training_ID
WHERE dlk.dlk_AssessDate >= '2015/01/01' AND dlk.dlk_AssessDate <= '2015/12/01'


AND

SELECT dmg.dmg_FirstName,
dmg.dmg_Surname,
lnk.lnk_ID,
dlk.dlk_AssessDate,
dmg.dmg_Sex,
trn.ScotHealthboard
FROM DAILY_LINK dlk
JOIN Absent ab ON ab.ScotRefID = dlk.dlk_ID
JOIN Link lnk ON lnk.lnk_ID = ab.Person_ID
JOIN Demographic dmg ON dmg.dmg_ID = lnk.lnk_dmgID
JOIN Training trn ON trn.Trn_ID = ab.Training_ID
WHERE dlk.dlk_AssessDate >= '2015/01/01' AND dlk.dlk_AssessDate <= '2015/12/01'
GROUP BY lnk.lnk_ID;

Answer
SELECT DISTINCT --< Here !
        dmg.dmg_FirstName,
        dmg.dmg_Surname,
        lnk.lnk_ID,
        dlk.dlk_AssessDate,
        dmg.dmg_Sex,
        trn.ScotHealthboard
FROM    DAILY_LINK dlk
JOIN    Absent ab ON ab.ScotRefID = dlk.dlk_ID
JOIN    Link lnk ON lnk.lnk_ID = ab.Person_ID
JOIN    Demographic dmg ON dmg.dmg_ID = lnk.lnk_dmgID
JOIN    Training trn ON trn.Trn_ID = ab.Training_ID
WHERE   dlk.dlk_AssessDate >= '2015/01/01' AND dlk.dlk_AssessDate <= '2015/12/01'
ORDER BY dmg.dmg_Surname, dmg.dmg_FirstName, lnk.lnk_ID;

OR

SELECT  MAX(dmg.dmg_FirstName  ) As dmg_FirstName, --< Aggregate
        MAX(dmg.dmg_Surname    ) As dmg_Surname,   --< all 
        lnk.lnk_ID,                                --< but GROUP BY expression
        MAX(dlk.dlk_AssessDate ) As dlk_AssessDate,
        MAX(dmg.dmg_Sex        ) As dmg_Sex,
        MAX(trn.ScotHealthboard) As ScotHealthboard
FROM    DAILY_LINK dlk
JOIN    Absent ab ON ab.ScotRefID = dlk.dlk_ID
JOIN    Link lnk ON lnk.lnk_ID = ab.Person_ID
JOIN    Demographic dmg ON dmg.dmg_ID = lnk.lnk_dmgID
JOIN    Training trn ON trn.Trn_ID = ab.Training_ID
WHERE   dlk.dlk_AssessDate >= '2015/01/01' AND dlk.dlk_AssessDate <= '2015/12/01'
GROUP BY lnk.lnk_ID;