I have 3 tables in MYSQL like:
rec_application=>AppId(PK),AppNum
rec_shortlistedCandidates=>ShortListId(PK),AppId(FK),TotalScore
rec_shortListedScore=>AppId(FK),TestId,Score,Comment
I want to list the Shortlisted candidates with there AppNum,Score and Comment for the selected TestId[on a dropdown change].The TestId can or can't be there in the 'rec_shortListedScore',if not present then Score=0 and Comment=null.
i tried :
SELECT S.AppId,SL.Score,SL.Comments,A.AppNum
FROM rec_shortlistedcandidates S
INNER JOIN rec_application A ON A.AppId=S.AppId
left JOIN rec_shortlistscore SL ON SL.AppId=S.AppId;
but this displays only when the 'rec_shortlistscore' have data in it.
解决方案A left outer join will do this. It returns all records from the right table, with nulls if the left table has no match
SQL Wizardry Part One - Joins[^] is my first SQL article, and it explains it.