PostgreSQL LEFT OUTER JOIN查询语法 [英] PostgreSQL LEFT OUTER JOIN query syntax

问题描述

让我说我有一个table1:

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

还有一个table2，其中第一个具有外键:

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

我该如何实现?

我已经尝试过:

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

已解决:感谢Mahmoud Gamal :(加上GROUP BY) 解决了该查询

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

推荐答案

您必须使用CROSS JOIN来获取第一个表中name和第二个表中option的所有可能组合.然后LEFT JOIN将这些组合与第二张表一起使用.像这样:

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

这篇关于PostgreSQL LEFT OUTER JOIN查询语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！