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

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

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

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

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

[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