2

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.

Typel
  • 1,109
  • 1
  • 11
  • 34

1 Answers1

2

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 = (?)
             );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You're a genius Gordon - that's really helpful, thanks! I'm still not getting any results but at least it's not giving me an error anymore... No more wasting time with that IN clause. I'll give credit as soon as I can figure out what piece is still missing to get the Price – Typel Jul 07 '17 at 23:15
  • @Typel . . . Your query is rather complicated. One possibility is that the weights don't match -- that seems like a number that would be more of a range. – Gordon Linoff Jul 07 '17 at 23:17
  • Good call, I had been using CEILING for weight in there at one point but must have tried dropping it after several mis-attempts. Also trying RTRIM on SalesNum and SalesType since those occasionally have blank characters at the end. Nothing yet but I feel like it's close... – Typel Jul 07 '17 at 23:23
  • Well at any rate, you've answered the original question - the rest is likely data tinkering on my part. Will close this up - thanks again for your help! – Typel Jul 07 '17 at 23:33