Jump to content

Left Join issue


dd_gamer

Recommended Posts

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 .

 

Link to comment
Share on other sites

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   

Link to comment
Share on other sites

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'

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.