How to do join on multiple criteria, returning all combinations of both criteria?

Question

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

Given:

• table1 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 1)
• table2 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 table1 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 table1 is basically a seating chart, and table2 is the meal option that each table/seat has chosen. Table1 has the convenient TableSeatID, but table2 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:

Answer 1

SELECT one.*, two.Meal
FROM table1 AS one
LEFT JOIN table2 AS two
     ON (one.WeddingTable = two.WeddingTable AND one.TableSeat = two.TableSeat);

Answer 2

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

Answer 3

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);

Answer 4

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

Answer 5

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.