Problem with join query

Question

I have two tables tracklist and song. I am fetching all values from song having sid as primary and tracklist having sid as foreign. Though fetching all values from song using this query is successfull.

SELECT t.sid as tsid, s.sid as ssid, s.name from song s LEFT JOIN tracklist t ON s.sid=t.sid;

But If I wish to fetch those values from song that are not in tracklist this query is not fetching single row.

SELECT t.sid as tsid, s.sid as ssid, s.name from song s LEFT JOIN tracklist t ON s.sid=t.sid where t.sid= NULL;

Have you tried using a JOIN or a INNER JOIN instead of a LEFT JOIN? — JMichelB
– JMichelB, Commented Aug 9, 2011 at 18:34
I don't know why your second query is failing (it looks ok to me) but I'd use an inner join for the first query. — Rag
– Rag, Commented Aug 9, 2011 at 18:37
@JMichel no it's legitimate to use a left join to find rows not in a table. The mysql doc even mentions it: "If you use LEFT JOIN to find rows that do not exist in some table ..." — Rag
– Rag, Commented Aug 9, 2011 at 18:38
For the second query, try using tsid instead of t.sid in the WHERE clause — JMichelB
– JMichelB, Commented Aug 9, 2011 at 18:50

Michael Berkowski · Accepted Answer · 2011-08-09 18:32:40Z

3

Rather than =NULL, use IS NULL:

SELECT t.sid as tsid, s.sid as ssid, s.name from song s LEFT JOIN tracklist t ON s.sid=t.sid where t.sid IS NULL;

answered Aug 9, 2011 at 18:32

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kevin Burton · Accepted Answer · 2011-08-09 19:28:40Z

2

I think you want to use IS NULL not = NULL

e.g:

SELECT t.sid as tsid, s.sid as ssid, s.name 
FROM song s 
LEFT JOIN tracklist t ON s.sid=t.sid 
WHERE t.sid IS NULL

edited Aug 9, 2011 at 19:28

answered Aug 9, 2011 at 18:33

Kevin Burton

12k2 gold badges26 silver badges37 bronze badges

Comments

anil.vemulapalli · Accepted Answer · 2011-08-09 18:34:35Z

0

I think this should work:

SELECT t.sid as tsid, s.sid as ssid, s.name 
from song s LEFT OUTER JOIN tracklist t ON s.sid=t.sid where t.sid IS NULL;

answered Aug 9, 2011 at 18:34

anil.vemulapalli

213 bronze badges

2 Comments

JMichelB Over a year ago

LEFT OUTER JOIN is just another way of using LEFT JOIN. Some language use one, some the other, not both.

anil.vemulapalli Over a year ago

I use (+) myself in oracle, LEFT JOIN/INNER JOIN ... terminology always confuses me. Thanks for the correction. The idea is to select everything from song but only tracklist which are null

Michael Berkowski · Accepted Answer · 2011-08-15 10:46:44Z

0

SELECT t.sid as tsid, s.sid as ssid, s.name from song s LEFT JOIN tracklist t ON s.sid<>t.sid;

its checking if the id of s.sid is null. if you didn't give the id then its not going to return it.

<> means not equal...

edited Aug 15, 2011 at 10:46

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

answered Aug 9, 2011 at 18:33

Kevin

3,5094 gold badges34 silver badges47 bronze badges

Collectives™ on Stack Overflow

Problem with join query

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related