Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: 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 09:17.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •