Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Posts
    12

    Unanswered: Beyond the Left Outer Join

    I'm probably missing something really small here but I have an issue that's bugging me. I tried to Google it but came up empty handed so far. I want to process some logic on the next table after a left outer join. I'm trying to get a query of all clients with or without phone numbers. I'm using a left outer join to achieve this. However, the client can have multiple phone numbers. I only care about their work one.

    So the tables look like this.

    Table 1: Client
    ClientID Name
    1 Steve

    Table 2: ClientPhoneJoin
    ClientID Phone ID
    1 1
    1 2

    Table 3: Phone
    PhoneID Phone# PhoneTypeID
    1 555-333-4444 1
    2 555-222-4323 2

    When I run:
    SELECT a.name, c.phonenumber
    from db.client a
    left outer join db.clientphonejoin b on b.clientid = a.clientid
    left outer join db.phone c on c.phoneid = b.phoneid
    where
    a.clientid = 1

    I get both phone numbers like this:
    Name PhoneNumber
    Steve 555-333-4444
    Steve 555-222-4323

    which is fine. But how do I only see phone numbers of type 1? If I try this:

    SELECT a.name, c.phonenumber
    from db.client a
    left outer join db.clientphonejoin b on b.clientid = a.clientid
    left outer join db.phone c on c.phoneid = b.phoneid and phonetypeid = 1
    where
    a.clientid = 1

    I get this:
    Name PhoneNumber
    Steve 555-333-4444
    Steve

    I still get 2 records because there were 2 records in the join however the second phone number is missing. Since I can't ask for the phonetype from the join, how do I reduce this back to one record?

    I can't just start my query in the join as the client is joined to another bigger table and if I write a query from that one, I'll need to get to the phone number through client, clientphonejoin, and phone tables anyway.

    I'm thinking I'm handling the left outer join incorrectly. How do I process conditions in a table beyond the left outer join?

    I'm on DB2/LINUXX8664 9.1.3 but I think this is a straight SQL question.

    Thanks for your assistance.

    --Ethan

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT c.name
         , x.phonenumber
      FROM db.client AS c
    LEFT OUTER 
      JOIN ( SELECT cp.clientid
                  , cp.phoneid 
                  , p.phonenumber
               FROM db.clientphonejoin AS cp 
             INNER
               JOIN db.phone AS p
                 on p.phoneid = cp.phoneid 
                and p.phonetypeid = 1
           ) AS x
        ON x.clientid = c.clientid
     WHERE c.clientid = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      Client(ClientID, Name) AS (
    VALUES
      (1, 'Steve')
    , (2, 'Tonkuma')
    , (3, 'Updike')
    )
    , ClientPhoneJoin(ClientID, PhoneID) AS (
    VALUES
      (1, 1)
    , (1, 2)
    , (2, 3)
    , (3, 4)
    )
    , Phone(PhoneID, PhoneNumber, PhoneTypeID) AS (
    VALUES
      (1, '555-333-4444', 1)
    , (2, '555-222-4323', 2)
    , (3, 'xxx-xxx-xxxx', 2)
    , (4, 'yyy-yyy-yyyy', 1)
    )
    SELECT a.name, c.phonenumber
      FROM client          a
      LEFT OUTER JOIN
           clientphonejoin b
      LEFT OUTER JOIN
           phone           c
       ON  c.phoneid  = b.phoneid 
       ON  b.clientid = a.clientid
       AND c.phonetypeid = 1
     WHERE
           a.clientid IN (1, 2, 3);
    ------------------------------------------------------------------------------
    
    NAME    PHONENUMBER 
    ------- ------------
    Steve   555-333-4444
    Tonkuma -           
    Updike  yyy-yyy-yyyy
    
      3 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    r937 wrotes
    Code:
    SELECT c.name
         , x.phonenumber
      FROM db.client AS c
    LEFT OUTER 
      JOIN ( SELECT cp.clientid
                  , cp.phoneid 
                  , p.phonenumber
               FROM db.clientphonejoin AS cp 
             INNER
               JOIN db.phone AS p
                 on p.phoneid = cp.phoneid 
                and p.phonetypeid = 1
           ) AS x
        ON x.clientid = c.clientid
     WHERE c.clientid = 1
    It's not necessary to use nested table expression.

    Position of ON clauses defines sequence of JOINs.

    a LEFT OUTER JOIN b INNER JOIN c ON ... ON ...
    is equivalent to
    a LEFT OUTER JOIN (b INNER JOIN c ON ...) ON ...

    Code:
    SELECT a.name, c.phonenumber
      FROM client          a
      LEFT OUTER JOIN
           clientphonejoin b
      INNER JOIN
           phone           c
       ON  c.phoneid   = b.phoneid 
       AND phonetypeid = 1
       ON  b.clientid  = a.clientid
     WHERE
           a.clientid IN (1, 2, 3)
    ;
    Last edited by tonkuma; 01-29-10 at 15:50. Reason: Adjust blanks.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your query
    SELECT a.name, c.phonenumber
    from db.client a
    left outer join db.clientphonejoin b on b.clientid = a.clientid
    left outer join db.phone c on c.phoneid = b.phoneid and phonetypeid = 1
    where
    a.clientid = 1
    is equivalent to
    (a left outer join b on ...) left outer join c on ...
    So, you got (1, 'Steve', 1, 1) and (1, 'Steve', 1, 2) with "(a left outer join b on ...)",
    then added to the temporary result rows another columns from c with "left outer join c on ...".

    My first example is equivalent to
    a LEFT OUTER JOIN (b LEFT OUTER JOIN c ON ...) ON ... AND c.phonetypeid = 1.
    So, I got (1, 1, 1, '555-333-4444', 1), (1, 2, 2, '555-222-4323', 2) for (1, 'Steve') with "(b LEFT OUTER JOIN c ON ...)",
    then got only (1, 'Steve', 1, 1, 1, '555-333-4444', 1) with "a LEFT OUTER JOIN (temporary result) ON ... AND c.phonetypeid = 1".

    I hope that this explanation something meaningful for you to understand outer join.

  6. #6
    Join Date
    Jan 2007
    Posts
    12
    Thanks guys! I tried tonkuma's suggestion and it worked awesomely. I was really hoping not to do sub selects as I've already tried to get around this problem that way before and always thought there had to be an easier solution.

    It was hard to explain what I was looking for to Google. dBforums never lets me down.

    Thanks again!

    --Ethan

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you compared my second example and my first example,
    you would see the position of "AND phonetypeid = 1" was different, like this.

    first example:
    a LEFT OUTER JOIN (b LEFT OUTER JOIN c ON predicate-1) ON predicate-2 AND c.phonetypeid = 1

    second example(modified r937):
    a LEFT OUTER JOIN (b INNER JOIN c ON predicate-1 AND phonetypeid = 1) ON predicate-2

    Those showed a difference of INNER JOIN and OUTER JOIN.
    The predicate AND phonetypeid = 1 was applicable earlier by using INNER JOIN in second example.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Those showed a difference of INNER JOIN and OUTER JOIN.
    The predicate AND phonetypeid = 1 was applicable earlier by using INNER JOIN in second example.
    Some examples....

    Test data:
    Code:
    WITH
      Client(ClientID, Name) AS (
    VALUES
      (1, 'Steve')
    , (2, 'Tonkuma')
    , (3, 'Updike')
    )
    , ClientPhoneJoin(ClientID, PhoneID) AS (
    VALUES
      (1, 1)
    , (1, 2)
    , (2, 3)
    , (3, 4)
    )
    , Phone(PhoneID, PhoneNumber, PhoneTypeID) AS (
    VALUES
      (1, '555-333-4444', 1)
    , (2, '555-222-4323', 2)
    , (3, 'xxx-xxx-xxxx', 2)
    , (4, 'yyy-yyy-yyyy', 1)
    )
    Two LEFT OUTER JOIN:
    Code:
    SELECT a.name, c.phonenumber
      FROM client          a
      LEFT OUTER JOIN
           clientphonejoin b
      LEFT OUTER JOIN
           phone           c
       ON  c.phoneid  = b.phoneid 
       ON  b.clientid = a.clientid
       AND c.phonetypeid = 1
     WHERE
           a.clientid IN (1, 2, 3)
    ;
    ------------------------------------------------------------------------------
    
    NAME    PHONENUMBER 
    ------- ------------
    Steve   555-333-4444
    Tonkuma -           
    Updike  yyy-yyy-yyyy
    
      3 record(s) selected.
    ***Wrong: Extra row was returned. ***
    Two LEFT OUTER JOIN(moved "AND phonetypeid = 1"):

    Code:
    SELECT a.name, c.phonenumber
      FROM client          a
      LEFT OUTER JOIN
           clientphonejoin b
      LEFT OUTER JOIN
           phone           c
       ON  c.phoneid  = b.phoneid 
       AND c.phonetypeid = 1
       ON  b.clientid = a.clientid
     WHERE
           a.clientid IN (1, 2, 3)
    ;
    ------------------------------------------------------------------------------
    
    NAME    PHONENUMBER 
    ------- ------------
    Steve   555-333-4444
    Steve   -           
    Tonkuma -           
    Updike  yyy-yyy-yyyy
    
      4 record(s) selected.
    Chang second LEFT OUTER JOIN to INNER JOIN:
    Code:
    SELECT a.name, c.phonenumber
      FROM client          a
      LEFT OUTER JOIN
           clientphonejoin b
      INNER JOIN
           phone           c
       ON  c.phoneid  = b.phoneid 
       AND c.phonetypeid = 1
       ON  b.clientid = a.clientid
     WHERE
           a.clientid IN (1, 2, 3)
    ;
    ------------------------------------------------------------------------------
    
    NAME    PHONENUMBER 
    ------- ------------
    Steve   555-333-4444
    Tonkuma -           
    Updike  yyy-yyy-yyyy
    
      3 record(s) selected.
    LEFT OUTER JOIN and INNER JOIN(returned the position of "AND phonetypeid = 1"):
    Code:
    SELECT a.name, c.phonenumber
      FROM client          a
      LEFT OUTER JOIN
           clientphonejoin b
      INNER JOIN
           phone           c
       ON  c.phoneid  = b.phoneid 
       ON  b.clientid = a.clientid
       AND c.phonetypeid = 1
     WHERE
           a.clientid IN (1, 2, 3)
    ;
    ------------------------------------------------------------------------------
    
    NAME    PHONENUMBER 
    ------- ------------
    Steve   555-333-4444
    Tonkuma -           
    Updike  yyy-yyy-yyyy
    
      3 record(s) selected.
    Last edited by tonkuma; 01-31-10 at 18:15. Reason: Added 4th example.

Posting Permissions

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