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:
5 Answers
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 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); 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 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.
