How to do join on multiple criteria, returning all combinations of both criteria
I am willing to bet that this is a really simple answer as I am a noob to SQL.
table 1 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 1)
table 2 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 2 specific to table2.criteria2)
There can be anywhere from 1 - 5 values of criteria 2 for each criteria 1 on the table.
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
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?
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.
Sample Data:
The results needs to show all 4 lines, being all 3 seats at WeddingTable 001 and the one seat at WeddingTable 002.
Desired Results:
select one.*, two.meal
from table1 as one
left join table2 as two
on (one.weddingtable = two.weddingtable and one.tableseat = two.tableseat)
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
- SEE SQLFiddle Demo
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);