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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select one    two meal from table1 as one left join table2 as two on  one weddingtable   two weddingtable and one tableseat   two tableseat

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

Examples related to sql

Examples related to sql-server

Examples related to join