如何在多个条件上进行连接，返回两个条件的所有组合？

我愿意打赌，这是一个非常简单的答案，因为我是 SQL 的菜鸟。

给出:

  • Table1有第1列(条件1)第2列(条件2)第3列 (公制1)
  • Table2有第1列(条件1)第2列(条件2)第3列 (特定于表2.Critia2的度量标准2)

对于表中的每个标准1，标准2可以有1-5个值。

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

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个组合中的一个。我怎样才能得到所有的组合？

假设有一场婚礼，餐桌1基本上是一张座位表，餐桌2是每张餐桌/每个座位选择的用餐选择。表1具有方便的 TableSeatID，但是表2没有可比的 ID。

数据样本:

enter image description here

结果需要显示所有4条线路，即所有3个座位在 WeddingTable001和一个座位在 WeddingTable002。

预期效果:

enter image description here

343235 次浏览
小开

It sounds like you want to list all the metrics?

SELECT Criteria1, Criteria2, Metric1 As Metric
FROM Table1
UNION ALL
SELECT Criteria1, Criteria2, Metric2 As Metric
FROM Table2
ORDER BY 1, 2

If you only want one Criteria1+Criteria2 combination, group them:

SELECT Criteria1, Criteia2, SUM(Metric) AS Metric
FROM (
SELECT Criteria1, Criteria2, Metric1 As Metric
FROM Table1
UNION ALL
SELECT Criteria1, Criteria2, Metric2 As Metric
FROM Table2
)
ORDER BY Criteria1, Criteria2
小开 
SELECT  aa.*,
bb.meal
FROM    table1 aa
INNER JOIN table2 bb
ON aa.tableseat = bb.tableseat AND
aa.weddingtable = bb.weddingtable
INNER JOIN
(
SELECT  a.tableSeat
FROM    table1 a
INNER JOIN table2 b
ON a.tableseat = b.tableseat AND
a.weddingtable = b.weddingtable
WHERE b.meal IN ('chicken', 'steak')
GROUP by a.tableSeat
HAVING COUNT(DISTINCT b.Meal) = 2
) c ON aa.tableseat = c.tableSeat
小开 
create table a1
(weddingTable INT(3),
tableSeat INT(3),
tableSeatID INT(6),
Name varchar(10));


insert into a1
(weddingTable, tableSeat, tableSeatID, Name)
values (001,001,001001,'Bob'),
(001,002,001002,'Joe'),
(001,003,001003,'Dan'),
(002,001,002001,'Mark');


create table a2
(weddingTable int(3),
tableSeat int(3),
Meal varchar(10));


insert into a2
(weddingTable, tableSeat, Meal)
values
(001,001,'Chicken'),
(001,002,'Steak'),
(001,003,'Salmon'),
(002,001,'Steak');


select x.*, y.Meal


from a1 as x
JOIN a2 as y ON (x.weddingTable = y.weddingTable) AND (x.tableSeat = y. tableSeat);
小开
最佳答案 
SELECT one.*, two.Meal
FROM table1 AS one
LEFT JOIN table2 AS two
ON (one.WeddingTable = two.WeddingTable AND one.TableSeat = two.TableSeat);
小开

First, I would suggest being more explicit with referencing column names. Since WeddingTable and TableSeat appear as column names in both tables and depending on your environment, there may be ambiguity.

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

Once I resolve this ambiguity in my environment, the INNER JOIN returns 4 records for the data listed in your tables. There are 4 records that match between the two tables.

From the sample provided, I fail to see why you are only obtaining one combination. The only other possibility I can think of is if the values in the WeddingTable and TableSeat columns of your actual data tables, in fact, do not match.

For example, assuming TableSeat is CHAR type and table1.TableSeat contains ('001', '002', '003', '001') and table2.TableSeat contains ('01', '002', '3', '01'), then this would be a situation where it would limit to one match due to the TableSeat component of the ON predicate.

Other considerations are for when there is data in one table that has no match in the other.

Anom's response uses a LEFT OUTER JOIN. This returns all records from table1 regardless of whether there is a match in table2. Where there is no match, the Meal column will contain a NULL value. Since all records match, the result is the same as the INNER JOIN.

However, adding a record to table1 for which there is no match in table2

INSERT INTO
table1 (WeddingTable, TableSeat, TableSeatID, Name)
VALUES
(003, 002, 003002, 'Arielle');

the LEFT OUTER JOIN query will now produce a different result than the INNER JOIN.


提交答案