If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Help with left join...

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 05-16-04, 08:12
pearl2 pearl2 is offline
Registered User
 
Join Date: Nov 2003
Location: Sinapore
Posts: 187
Help with left join...

Hi,

I've the following table specs and the sample data for each of the tables:
Code:
CREATE TABLE tbl1 (
      mem_id MEDIUMINT UNSIGNED NOT NULL,
      cat CHAR(3) NOT NULL,
      item_id SMALLINT UNSIGNED NOT NULL,
);

CREATE TABLE tbl2 (
      cat CHAR(3) NOT NULL,
      item_id MEDIUMINT UNSIGNED NOT NULL,
      item TINYTEXT NOT NULL,
);

CREATE TABLE tbl3 (
      mem_id MEDIUMINT UNSIGNED NOT NULL,
      cat CHAR(3) NOT NULL,
      item_id MEDIUMINT UNSIGNED NOT NULL,
      selected TINYTEXT NOT NULL,
);

#tbl1
INSERT INTO tbl1 VALUES ('1', '201', '1');
INSERT INTO tbl1 VALUES ('1', '201', '3');
INSERT INTO tbl1 VALUES ('2', '201', '1');
INSERT INTO tbl1 VALUES ('2', '201', '2');
INSERT INTO tbl1 VALUES ('3', '201', '1');

#tbl2
INSERT INTO tbl2 VALUES ('201', '1', 'this is item1');
INSERT INTO tbl2 VALUES ('201', '2', 'this is item2');
INSERT INTO tbl2 VALUES ('201', '3', 'this is item3');

#tbl3
INSERT INTO tbl3 VALUES ('1', '201', '1', 'selected1');
INSERT INTO tbl3 VALUES ('2', '201', '2', 'selected2');
I then do the following query:
Code:
SELECT tbl1.mem_id, tbl1.cat, item, selected
  FROM tbl1
  LEFT JOIN tbl3 USING (mem_id, cat, item_id)
  LEFT JOIN tbl2 USING (cat, item_id)
  WHERE tbl1.mem_id=2
The result I got was:
Code:
mem_id cat  item              selected
2      201   NULL             NULL
2      201   this is item2    selected2
I need the output to be:
Code:
mem_id cat  item              selected
2      201   this is item1    NULL
2      201   this is item2    selected2
Could someone enlighten me on how to accomplish that?

Thanks in advance

Last edited by pearl2; 05-16-04 at 08:17.
Reply With Quote
  #2 (permalink)  
Old 05-16-04, 09:16
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
this is a perfect example of one of the reasons i never use USING

it can be ambiguous

which columns are used to join to tbl2, tbl1's or tbl3's?

rewrite your query with explicit join conditions
Code:
select tbl1.mem_id    as mem1
     , tbl1.cat       as cat1 
     , tbl1.item_id   as item1
     , tbl3.mem_id    as mem3
     , tbl3.cat       as cat3
     , tbl3.item_id   as item3
     , tbl3.selected
     , tbl2.cat       as cat2
     , tbl2.item_id   as item2 
     , tbl2.item
  from tbl1
left outer
  join tbl3 
    on tbl1.mem_id  = tbl3.mem_id
   and tbl1.cat     = tbl3.cat
   and tbl1.item_id = tbl3.item_id
left outer
  join tbl2 
   on  tbl1.cat     = tbl2.cat
   and tbl1.item_id = tbl2.item_id
 where tbl1.mem_id=2
run this query and pay attention to the values in all fields
Code:
mem1 cat1 item1 mem3 cat3 item3 selected  cat2 item2 item
  2  201    1   NULL NULL NULL  NULL      201    1   this is item1
  2  201    2    2   201   2    selected2 201    2   this is item2
now change the conditions in the last ON clause to match tbl3 fields to tbl2 fields instead

Code:
mem1 cat1 item1 mem3 cat3 item3 selected  cat2 item2 item
  2  201    1   NULL NULL NULL  NULL      NULL NULL  NULL
  2  201    2    2   201  2     selected2 201    2   this is item2
see the difference?

don't use USING!

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 05-16-04, 09:49
pearl2 pearl2 is offline
Registered User
 
Join Date: Nov 2003
Location: Sinapore
Posts: 187
THANK YOU SO MUCH, r937!

You really made my day

Since about 4 hrs ago, I was trying to see how to fix that.

I became paranoid and was thinking perhaps I didn't really set up the tables correctly or something like that.

Thanks thanks!!

I'll remember to use ON in place of USING.

cheers,
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On