PostgreSQL LEFT OUTER JOIN query syntax

kosta picture kosta · Mar 25, 2013 · Viewed 30.6k times · Source

Lets say I have a table1:

  id      name
-------------
  1       "one"
  2       "two"
  3       "three"

And a table2 with a foreign key to the first:

id    tbl1_fk    option   value
-------------------------------
 1      1         1        1
 2      2         1        1
 3      1         2        1
 4      3         2        1

Now I want to have as a query result:

table1.id | table1.name | option | value
-------------------------------------
      1       "one"        1       1
      2       "two"        1       1
      3       "three"    
      1       "one"        2       1
      2       "two"    
      3       "three"      2       1

How do I achieve that?

I already tried:

SELECT
  table1.id,
  table1.name,
  table2.option,
  table2.value
FROM table1 AS table1
LEFT outer JOIN table2 AS table2 ON table1.id = table2.tbl1fk

but the result seems to omit the null vales:

1    "one"    1   1
2    "two"    1   1
1    "one"    2   1
3    "three"  2   1

SOLVED: thanks to Mahmoud Gamal: (plus the GROUP BY) Solved with this query

SELECT 
  t1.id,
  t1.name,
  t2.option,
  t2.value
FROM
(
  SELECT t1.id, t1.name, t2.option
  FROM table1 AS t1
  CROSS JOIN table2 AS t2
) AS t1
LEFT JOIN table2 AS t2  ON t1.id = t2.tbl1fk
                       AND t1.option = t2.option
group by t1.id, t1.name, t2.option, t2.value
ORDER BY t1.id, t1.name

Answer

Mahmoud Gamal picture Mahmoud Gamal · Mar 25, 2013

You have to use CROSS JOIN to get every possible combination of name from the first table with the option from the second table. Then LEFT JOIN these combination with the second table. Something like:

SELECT 
  t1.id,
  t1.name,
  t2.option,
  t2.value
FROM
(
  SELECT t1.id, t1.name, t2.option
  FROM table1 AS t1
  CROSS JOIN table2 AS t2
) AS t1
LEFT JOIN table2 AS t2  ON t1.id = t2.tbl1_fk
                       AND t1.option = t2.option

SQL Fiddle Demo