Beyond the Left Outer Join

ethansmith · #1 (**permalink**) 01-29-10, 13:47

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

r937 · #2 (**permalink**) 01-29-10, 14:14

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

tonkuma · #3 (**permalink**) 01-29-10, 14:34

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.

tonkuma · #4 (**permalink**) 01-29-10, 14:46

Quote:

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)
;

tonkuma · #5 (**permalink**) 01-29-10, 15:30

Your query

Quote:

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.

ethansmith · #6 (**permalink**) 01-31-10, 10:58

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

tonkuma · #7 (**permalink**) 01-31-10, 16:42

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.

tonkuma · #8 (**permalink**) 01-31-10, 17:04

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech