MySQL Help, LEFT JOIN not working !!

[iosoft]

Hello Friends,

The main problem is that MySQL LEFT JOIN is no working. It is giving the output same as NORMAL JOIN !!


Example -

Table TB1 -

ID NAME
------------------------------
1 AAAA
2 BBBB

Table TB2 -

ID SUBJECT
---------------------------------
1 MATH
1 CHEM
2 MATH
2 PHYS

Normal JOIN:

SELECT tb1.name,tb2.subject FROM tb1,tb2 WHERE tb1.id=tb2.id AND tb2.subject='CHEM';

OUTPUT:

AAAA CHEM

Now using LEFT JOIN:

SELECT tb1.name,tb2.subject FROM tb1 LEFT JOIN tb2  ON tb1.id=tb2.id WHERE tb2.subject='CHEM';

I should Give -

AAAA CHEM
BBBB NULL

But giving -

AAAA CHEM

Whats wrong I am doing ??!!

 

[qxsilvr]

try doing a LEFT OUTER JOIN

 

[iosoft]

Oh, I got it, the query should be -

SELECT tb1.id, tb1.name, tb2.subject
FROM tb1
LEFT JOIN tb2 ON ( tb1.id = tb2.id
AND tb2.subject = 'COMP' ) ;

 

[major9]

It should be like this -

SELECT tb1.id, tb1.name, tb2.subject

FROM tb1

LEFT JOIN tb2 ON ( tb1.id = tb2.id)

WHERE tb2.subject = 'COMP';