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:

Weakfish

Show sample data, expected results and actual results. – Muldoon

Do you want values that match EITHER of the conditions? If so, use an OR instead of the AND – Ornamented

@Weakfish please provide sample data, we don't know where you want to search the value for your criteria. – Maximilien

Your AND in the join condition is correct based on your sample data. If you are not getting the correct result back, you may have some other problem in your query. Please post your actual query as you have attempted it... – Hendley

@MichaelBerkowski Full query is in there. – Weakfish

Your original query works for me. – Jarman

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

Balboa

could you please explain the difference? – Lawyer

This answer gives you all the data from Table 1 and only the data that matches (the on clause) from Table 2. It enables two.meal to be picked out for the same weddingtable and tableseat. The difference would come if Table 1 had data for a weddingtable and tableseat that did not exist in Table 2. Since this is a left join, you would get a null value for two.meal for the unmatched output row of Table 1. – Oberheim

Does anybody know why this query is so slow? – Kalb

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

Maximilien

While I will definitely keep that website in mind for future problem solving, the results rendered are what I am currently getting, not what I need them to be. The results should be 4 rows worth of data. – Weakfish

The line WHERE b.meal IN ('chicken', 'steak') can't be, because I simply can't type out the over 2000 possibilities that this particular field has in my real data. – Weakfish

then you will handle it in the application layer. if you think it can't be, then how will you search for it? – Maximilien

How do I dynamically get the 2 in the line HAVING COUNT(DISTINCT b.Meal) = 2? and how I can I add ('chicken', 'steak') dynamically? (aka all possible values in b.Meal) – Weakfish

you can get ideas from here, PHP/MYSQL using an array in WHERE clause – Maximilien

Also, the updated query shows table 1 seat 1 bob twice, with the meal column being one of Chicken and one of Steak. For the lack of a better term, I need the query to traverse all table/seat combinations and get the name/meal for that table/seat criteria. – Weakfish

@Weakfish why is wedding table2 present? there was no chicken on the meal right? – Maximilien

The wedding example, is not the actual data. This is just a set of data to demonstrate the fact that I am only getting the 2 lines in my sample data picture, while I need what is pictured in the desired results. So focus more on making sure the Table/Seat combos are in the results, rather than this being an actual wedding menu. – Weakfish

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

Callipash

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

Claude

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.

Bundelkhand