Double sided JOIN

gtk · #1 (**permalink**) 03-21-06, 09:00

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

r937 · #2 (**permalink**) 03-22-06, 11:01

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

gtk · #3 (**permalink**) 03-23-06, 03:43

Ok thanks
FULL OUTER JOIN is SQL ?

r937 · #4 (**permalink**) 03-23-06, 06:41

yes, FULL OUTER JOIN is SQL

mysql doesn't support it, other database systems do

Internet Services

Web Development

Digital Marketing

Consumer Tech