Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: Double sided JOIN

    Lets say we have 2 tables

    Code:
    +---------+-----------+---------+
    | user_id | user_name | role_id |
    +---------+-----------+---------+
    |       1 | Peter     |       3 |
    |       2 | Paul      |       1 |
    |       3 | Jake      |       4 |
    +---------+-----------+---------+
    
    and
    
    +---------+-----------+
    | role_id | role_name |
    +---------+-----------+
    |       1 | user      |
    |       2 | moderator |
    |       3 | root      |
    +---------+-----------+
    I'd like to JOIN them by their 2 sides in order to get something like

    Code:
    +-----------+-----------+
    | user_name | role_name |
    +-----------+-----------+
    | Peter     | root      |
    | Paul      | user      |
    | Jake      | NULL      |
    | NULL      | moderator |
    +-----------+-----------+
    By doing something like

    Code:
    SELECT
     t1.user_name AS "user_name",
     t2.role_name AS "role_name"
    FROM
     t1 DOUBLE_LEFT JOIN t2
      ON (t1.role_id = t2.role_id)
    Is it possible or is it totally illogical (and why if its the case ?)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you have described is a FULL OUTER JOIN -- rows from either table, with or without matching row from the other table

    unfortunately mysql does not directly support FULL OUTER JOIN

    you can achieve the same result as follows:
    Code:
    select t1.user_name as "user_name"
         , t2.role_name as "role_name"
      from t1 
    left outer
      join t2
        on t2.role_id = t1.role_id
    union all    
    select null         as "user_name"
         , t2.role_name as "role_name"
      from t2 
    left outer
      join t1
        on t1.role_id = t2.role_id
     where t1.role_id is null
    Last edited by r937; 03-23-06 at 07:41.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Ok thanks
    FULL OUTER JOIN is SQL ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, FULL OUTER JOIN is SQL

    mysql doesn't support it, other database systems do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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