如何加入多个条件，返回两个条件的所有组合 [英] How to do join on multiple criteria, returning all combinations of both criteria

问题描述

我敢打赌这是一个非常简单的答案，因为我对SQL不熟悉.

I am willing to bet that this is a really simple answer as I am a noob to SQL.

表1具有第1列(标准1)，第2列(标准2)，第3列(度量1)

table 1 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 1)

表2具有第1列(标准1)，第2列(标准2)，第3列(特定于table2.criteria2的度量2)

table 2 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 2 specific to table2.criteria2)

表中的每个条件1可以有1-5个条件2的值.

There can be anywhere from 1 - 5 values of criteria 2 for each criteria 1 on the table.

当我在此处使用join语句时(假设我在此之前将表1标识为1):

when I use the join statement here (assuming I identify table 1 as One prior to this):

Select WeddingTable, TableSeat, TableSeatID, Name, Two.Meal
FROM table1 as One
inner join table2 as Two
on One.WeddingTable = Two.WeddingTable and One.TableSeat = Two.TableSeat

即使我知道存在3或4个事实，我也只能获得条件1/条件2组合之一.

I only get one of the criteria 1/criteria 2 combinations even when I know for a fact that there are 3 or 4. How do I get all combinations?

以举行婚礼的情况为例，表1基本上是座位表，表2是每个表/座位都选择的用餐选项.表1具有方便的TableSeatID，但表2没有可比的ID.

Take the situation where there is a wedding where table 1 is basically a seating chart, and table 2 is the meal option that each table/seat has chosen. Table 1 has the convenient TableSeatID, but Table 2 does not have a comparable ID.

样本数据:

结果需要显示全部4行，即WeddingTable 001的所有3个席位和WeddingTable 002的一个席位.

The results needs to show all 4 lines, being all 3 seats at WeddingTable 001 and the one seat at WeddingTable 002.

所需结果:

推荐答案

select one.*, two.meal
from table1 as one
left join table2 as two
on (one.weddingtable = two.weddingtable and one.tableseat = two.tableseat)

这篇关于如何加入多个条件，返回两个条件的所有组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！