Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    2

    Unanswered: Left Outer Join To Self

    Greetings,

    I am having some problems getting a left outer join to work against itself. Essentially, I am trying to determine the delta of software titles owned by a user as compared to another user.

    My table schema is as follows:

    tbl_user_software
    ===========
    (PK) user_id
    (PK) software_id

    Some sample data:

    user1, 1
    user1, 2
    user2, 1
    user2, 2
    user2, 3
    user2, 4

    I would like to find all of the software that user2 has that user1 does not have. My desired result set:

    3
    4

    I figured a left outer join to an alias of tbl_user_software would be sufficient:

    select a.software_id, c.software_id
    from tbl_user_software a
    left outer join (select b.software_id from tbl_user_software b where b.user_id = 'user2') c
    on a.software_id = c.software_id
    where a.user_id = 'user1'
    and c.software_id = null;

    However, this returns no rows.

    I was wondering if anyone knows where I am going wrong here? I have also tries the NOT EXISTS clause without success either. I am a little baffled :-)

    Thanks in advance for any assistance!

    Cheers,
    Jason

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select a.software_id
         , b.software_id 
      from tbl_user_software a
    left outer 
      join tbl_user_software b 
        on a.software_id = b.software_id 
       and                 b.user_id = 'user2'
     where a.user_id = 'user1'
       and                 b.user_id is null
    rudy

  3. #3
    Join Date
    Apr 2003
    Posts
    2
    Rudy,

    Thanks for the help. However, that also returns 0 rows.

    Cheers,
    Jason

    Originally posted by r937
    Code:
    select a.software_id
         , b.software_id 
      from tbl_user_software a
    left outer 
      join tbl_user_software b 
        on a.software_id = b.software_id 
       and                 b.user_id = 'user2'
     where a.user_id = 'user1'
       and                 b.user_id is null
    rudy

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, silly me, i had the user_ids backwards
    Code:
    select a.user_id, a.software_id
         , b.software_id 
      from tbl_user_software a
    left outer 
      join tbl_user_software b 
        on a.software_id = b.software_id 
       and                 b.user_id = 'user1'
     where a.user_id = 'user2'
       and                 b.user_id is null
    rudy

Posting Permissions

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