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:
This question is related to
sql
sql-server
join
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);
Source: Stackoverflow.com