CODEWITHSUNDEEP
sqlsql-server
Typel
Typel

Reputation: 1139

SQL Server : multi-join with tuple IN clause

I'm trying to join 4 tables that have a somewhat complex relationship. Because of where this will be used, it needs to be contained in a single query, but I'm having trouble since the primary query and the IN clause query both join 2 tables together and the lookup is on two columns.

The goal is to input a SalesNum and SalesType and have it return the Price

Tables and relationships:

sdShipping

SalesNum[1]
SalesType[2]
Weight[3]

sdSales

SalesNum[1]
SalesType[2]
Zip[4]

spZones

Zip[4]
Zone[5]

spPrices

Zone[5]
Price
Weight[3]

Here's my latest attempt in T-SQL:

SELECT 
    spp.Price 
FROM 
    spZones AS spz 
LEFT OUTER JOIN 
    spPrices AS spp ON spz.Zone = spp.Zone 
WHERE 
    (spp.Weight, spz.Zip) IN (SELECT ship.Weight, sales.Zip 
                              FROM sdShipping AS ship 
                              LEFT OUTER JOIN sdSales AS sales ON sales.SalesNum = ship.SalesNum 
                                                               AND sales.SalesType = ship.SalesType
                              WHERE sales.SalesNum = (?) 
                                AND ship.SalesType = (?));

SQL Server Management Studio says I have an error in my syntax near ',' (appropriately useless error message). Does anybody have any idea whether this is even allowed in Microsoft's version of SQL? Is there perhaps another way to accomplish it? I've seen the multi-key IN questions answered on here, but never in the case where both sides require a JOIN.

Upvotes: 2

Views: 893

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Many databases do support IN on tuples. SQL Server is not one of them.

Use EXISTS instead:

SELECT spp.Price 
FROM spZones spz LEFT OUTER JOIN
     spPrices spp
     ON spz.Zone = spp.Zone 
WHERE EXISTS (SELECT 1
              FROM sdShipping ship LEFT JOIN
                   sdSales sales 
                   ON sales.SalesNum = ship.SalesNum AND
                      sales.SalesType = ship.SalesType
              WHERE spp.Weight = ship.Weight AND spz.Zip = sales.Zip AND
                    sales.SalesNum = (?) AND
                    ship.SalesType = (?)
             );

Upvotes: 2

Related Questions