Left Join issue

[dd_gamer]

I'm having a problem with a query using the 'Left Join". My results are still not showing all the records which is why I'm using the left join. Can someone show me what I'm doing wrong?

 

My Query:

SELECT 
ss.rc_id, 
ss.s_id, 
ss.sub_id, 
gsj.g_id,
gsj.gla,
gsj.sub_id,
gsj.rc_id,
s_name.id 

FROM ss

LEFT JOIN gsj 
ON ss.rc_id = gsj.rc_id AND ss.sub_id = gsj.sub_id

JOIN s_name
ON ss.sub_id = s_name.id

WHERE 
ss.rc_id = '71' AND
gsj.g_id = '1' AND
ss.s_id = '2250'

 

Tables and output:

TABLE = gsj

 

rc_id g_id sub_id  gla

 

71      1   3       1.8

71      1   5 2.51

71 1   6 2.45

71 1   24 1.04

71 1   25 1.8

71 1   9   NULL

 

____________________________________

 

TABLE = ss

 

rc_id s_id sub_id

 

71 2250 1

71 2250 3

71 2250 6

71 2250 25

71 2250 5

71 2250 2

 

____________________________________

 

OUTPUT

 

Rows: 4

rc_id s_id sub_id g_id gla sub_id

 

71 2250 3 1     1.8 3

71 2250 6 1     2.45 6

71 2250 25 1     1.8 25

71 2250 5 1     2.51 5

 

As you can see from the output I'm only getting 4 rows with results and I want to get all 6 rows from ss table ... I'm missing sub_id = 1 & 2 .

 

[dd_gamer]

Perhaps a little more detail of what results I'm looking for... I want to retrieve all the records from the "ss" table and only "gla" fields that match from the "gsj" table. So, the results I'm looking for would look like this (if it works):

 

Rows: 6

rc_id    s_id    sub_id    g_id    gla   

71    2250      1          1

71    2250      2          1     

71    2250      3          1        1.8     

71    2250      6          1        2.45     

71    2250      25        1        1.8     

71    2250      5          1        2.51   

[fenway]

LEFT JOIN twice -- see if thath elps.

[dd_gamer]

I added a second "Left Join" on  "JOIN s_name" but it didn't change anything. I took it out  because it wasn't needed for this example anyway and added to the confusion. I'm missing something here... So, here is the Query without the second JOIN...:

 

SELECT ss.rc_id, ss.s_id, ss.sub_id, gsj.g_id, gsj.gla, gsj.sub_id, gsj.rc_id

FROM ss

LEFT JOIN gsj 
ON ss.rc_id = gsj.rc_id AND ss.sub_id = gsj.sub_id

WHERE ss.rc_id = '71' AND gsj.g_id = '1' AND ss.s_id = '2250'

[fenway]

Ah yes -- you can't examine any conditions in the WHERE clause for LEFT JOIN'ed table columns -- move them to the ON condition.

[dd_gamer]

THANK you again fenway!