如何连接SQL表，包括无现有关系

I have a SQL Server join question.

我有一个SQL Server加入问题。

I have 3 tables:

我有3张桌子:

• one stores the restaurants

一个存储餐馆

• one stores "restaurant properties" like, "Wifi, Pool, Clean, All-Inclusive ..."

一个商店“餐厅物业”,如“无线网络,游泳池,清洁,全包......”

• and one stores the relationships between a restaurant and the properties it has

一个人存储餐馆和它拥有的房产之间的关系

In the end, I want to get a result table for one given restaurant which should list all the properties this restaurants HAS AND DOES NOT HAVE. So I need to output null values too.

最后,我想得到一个给定餐厅的结果表,该餐厅应该列出这家餐厅已经和没有的所有房产。所以我也需要输出空值。

| id     name         |    | idRes   idProp |  | id      prop  |
+---------------------+    +----------------+  +---------------+
| 01     restaurant-01|    | 1        1     |  | 1       wifi  |
| 02     restaurant-02|    | 1        2     |  | 2       pool  |
                           | 2        2     |  | 3       24/7  |
                           | 2        2     |  | 4       clean |

Now I want to output the restaurant with the ID 1 with all properties it has and does not have.

现在我想输出ID为1的餐厅,其中包含所有属性,但没有。

| id     name           idRes   idProp    id   prop  |
+----------------------------------------------------+
| 1      restaurant-1     1       1        1   wifi  |
| 1      restaurant-1     1       2        2   pool  |
| 1      restaurant-1     NULL    NULL     3   24/7  |
| 1      restaurant-1     NULL    NULL     4   clean |

I hope that this is even possible. Thank guys in advance. You are awesome.

我希望这甚至可能。提前谢谢你们。你太棒了。

1 个解决方案

#1

You want to know for all properties combined with the restaurant whether a relation exists. So cross join restaurants and properties and outer join the relations:

您想知道与餐馆相关的所有物业是否存在关系。所以交叉加入餐馆和属性和外部加入关系:

select *
from restaurant r
cross join property p
left join relation rp on rp.idres = r.id and rp.idprop = p.id
where r.id = 1;