dd_gamer Posted February 10, 2012 Share Posted February 10, 2012 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 . Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted February 11, 2012 Author Share Posted February 11, 2012 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted February 12, 2012 Share Posted February 12, 2012 LEFT JOIN twice -- see if thath elps. Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted February 13, 2012 Author Share Posted February 13, 2012 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' Quote Link to comment Share on other sites More sharing options...
fenway Posted February 13, 2012 Share Posted February 13, 2012 Ah yes -- you can't examine any conditions in the WHERE clause for LEFT JOIN'ed table columns -- move them to the ON condition. Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted February 13, 2012 Author Share Posted February 13, 2012 THANK you again fenway! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.