Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: Join a table multiple times

    Hello -

    I have a system where I have abstracted all of my contacts (various phone numbers, email addresses, etc) out to a 'contact' table.

    Code:
    member
    ------
    
    id
    fname
    lname
    
    contact
    -------
    
    id
    person_type
    person_id
    contact_type_id
    contact
    (Note that the 'contact' table also has a 'person_type' field. This is because I have another type of person (stored in a 'client' table). I would have excluded this from the example, for simplification purposes, but I'm leaving it in just in case it may be part or all of my problem.)

    The following query returns the expected data when email (c.contact) and phone (c2.contact) both exist, or are both null. But it returns the empty set in any other case.

    Code:
    select m.id id, m.fname fname, m.lname lname, c.contact email, c2.contact phone
      from member m
           left outer join contact c on c.person_id = m.id 
           left outer join contact c2 on c2.person_id = m.id 
     where m.id = 12
       and (c.person_type = 1 or c.person_type is null)
       and (c.contact_type_id = 1 or c.contact_type_id is null)
       and (c2.person_type = 1 or c2.person_type is null)
       and (c2.contact_type_id = 4 or c2.contact_type_id is null)
    If it makes any difference, I'm using MySQL.

    What am I doing wrong?

    Also looking for criticism on the choice of a single 'contact' table (for both member and client) versus a member_contact table and a client_contact table.

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AYMmike View Post
    What am I doing wrong?
    using a left outer join and then putting conditions on the right table into the WHERE clause

    put them into the ON clause instead
    Code:
    SELECT m.id 
         , m.fname 
         , m.lname 
         , c.contact email
         , c2.contact phone
      FROM member m
    LEFT OUTER 
      JOIN contact c 
        ON c.person_id = m.id 
       AND COALESCE(c.person_type,1) = 1
       AND COALESCE(c.contact_type_id,1) = 1
    LEFT OUTER 
      JOIN contact c2 
        ON c2.person_id = m.id 
       AND COALESCE(c2.person_type,1) = 1 
       AND COALESCE(c2.contact_type_id,4) = 4
     WHERE m.id = 12
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    4

    dang...

    I could have sworn that i tried this earlier:

    Code:
    select m.id id, m.fname fname, m.lname lname, c.contact email, c2.contact phone
      from member m
           left outer join contact c on c.person_id = m.id 
               and (c.contact_type_id = 1 or c.contact_type_id is null)
               and (c.person_type = 1 or c.person_type is null)
           left outer join contact c2 on c2.person_id = m.id 
               and (c2.person_type = 1 or c2.person_type is null)
               and (c2.contact_type_id = 4 or c2.contact_type_id is null)
     where m.id = 12
    It works, as does your statement.

    I haven't used coalesce before. Is there a particular advantage to using it vs 'or x is null'?

    Thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AYMmike View Post
    I haven't used coalesce before. Is there a particular advantage to using it vs 'or x is null'?
    it's shorter and simpler
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) If there is a row with contact_type_id is null for a m.id, it satisfy both of
    (c.contact_type_id = 1 or c.contact_type_id is null)
    and
    (c2.contact_type_id = 4 or c2.contact_type_id is null)

    So, email and phone would be same.


    (2) If there are a row with (contact_type_id is null) and a row with (contact_type_id = 1) for a m.id, both rows satisfy
    (c.contact_type_id = 1 or c.contact_type_id is null)

    So, two rows with same m.id would be in the results, like
    Code:
    m.id email     phone
    ---- --------- ----------
    id   c.contact <null>
    id   <null>    <null>
    Note: <null> corresponds to the value of contract of a row with (contact_type_id is null).


    (3) If there are a row with (contact_type_id is null), a row with (contact_type_id = 1), and a row with (contact_type_id = 4) for a m.id,

    result output may be like...
    Code:
    m.id email     phone
    ---- --------- ----------
    id   c.contact c2.contact
    id   c.contact <null>
    id   <null>    c2.contact
    id   <null>    <null>
    Note: <null> corresponds to the value of contract of a row with (contact_type_id is null).
    Last edited by tonkuma; 04-06-12 at 11:47. Reason: Add example for (2). Add Note for (3).

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This example might be able to avoid multiple output rows(case (2) and case (3) in my previous post).

    Example:
    Code:
    SELECT m.id
         , MAX(m.fname) AS fname
         , MAX(m.lname) AS lname
         , MAX(CASE COALESCE(c.contact_type_id , 1) WHEN 1 THEN c.contact END) AS email
         , MAX(CASE COALESCE(c.contact_type_id , 4) WHEN 4 THEN c.contact END) AS phone
     FROM  member   m
     LEFT  OUTER JOIN
           contact  c
      ON   c.person_id                     = m.id 
       AND COALESCE(c.person_type     , 1) = 1
       AND COALESCE(c.contact_type_id , 1) IN (1 , 4)
     WHERE m.id = 12
     GROUP BY
           m.id

  7. #7
    Join Date
    Apr 2012
    Posts
    4

    Never gonna happen...

    Thanks for the suggestion, tonkuma.

    The contact_type_id field is set to "not null" (in fact, all of the fields in the contact table are "not null"). So I should not encounter this issue.

    The reason I added the "is null" clauses to the select is for the case where the entire phone or email record does not exist.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The reason I added the "is null" clauses to the select is for the case where the entire phone or email record does not exist.
    Now, you have moved the conditions for person_type and contact_type_id to ON conditions.

    So, all "is null" are not neccesary, like...
    Code:
    select m.id id, m.fname fname, m.lname lname
         , c .contact email
         , c2.contact phone
      from member m
      left outer join contact c
       on  c .person_id = m.id 
       and c .person_type = 1
       and c .contact_type_id = 1
      left outer join contact c2
       on  c2.person_id = m.id 
       and c2.person_type = 1
       and c2.contact_type_id = 4
     where m.id = 12

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    or

    Example 2:
    Code:
    SELECT m.id
         , MAX(m.fname) AS fname
         , MAX(m.lname) AS lname
         , MAX(CASE c.contact_type_id WHEN 1 THEN c.contact END) AS email
         , MAX(CASE c.contact_type_id WHEN 4 THEN c.contact END) AS phone
     FROM  member   m
     LEFT  OUTER JOIN
           contact  c
      ON   c.person_id       = m.id 
       AND c.person_type     = 1
       AND c.contact_type_id IN (1 , 4)
     WHERE m.id = 12
     GROUP BY
           m.id

  10. #10
    Join Date
    Apr 2012
    Posts
    4

    you're right

    Quote Originally Posted by tonkuma View Post
    Now, you have moved the conditions for person_type and contact_type_id to ON conditions.

    So, all "is null" are not neccesary, like...
    Code:
    select m.id id, m.fname fname, m.lname lname
         , c .contact email
         , c2.contact phone
      from member m
      left outer join contact c
       on  c .person_id = m.id 
       and c .person_type = 1
       and c .contact_type_id = 1
      left outer join contact c2
       on  c2.person_id = m.id 
       and c2.person_type = 1
       and c2.contact_type_id = 4
     where m.id = 12
    That seems to work.

    Thanks

  11. #11
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    If you have nothing to contribute, there is no need to post. . .

Posting Permissions

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