mysql left join select all records from left and right table even if no records in rigth table

Question

0.00/5 (No votes)

See more:

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.

Posted 22-Jan-14 18:29pm

fiashieen

Add a Solution

1 solution

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)

Christian Graus · Accepted Answer · 2014-01-22T19:16:00

Solution 1

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.

Posted 22-Jan-14 19:16pm

Christian Graus

Updated 22-Jan-14 19:17pm

v2

Comments

fiashieen 23-Jan-14 3:29am

Thanks for you reply...but its not working, when i am checking with the condition as
"where TestId='1'".Actually i want the output on the basis of the TestId i am selecting.once the table rec_shortListedScore contains data this is working properly else its not...:(

Christian Graus 23-Jan-14 3:36am

If you're checking a condition that is in the first table, then you need to do where (TestId = 1 or TestId is null) because when it's null ( from the left join ), it does not equal 1.

fiashieen 23-Jan-14 4:04am

oh...may be problem is that, the TestId which i'm using for the condition is only in the table which is used for Left join.so i think i must use a "union" to solve the issue is it??