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

select one.*, two.mealfrom table1 as oneleft join table2 as twoon (one.weddingtable = two.weddingtable and one.tableseat = two.tableseat)

SELECT  aa.*,        bb.mealFROM    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.Mealfrom a1 as xJOIN a2 as y ON (x.weddingTable = y.weddingTable) AND (x.tableSeat = y. tableSeat);