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_IDI 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;
Source (Stackoverflow)