I am trying to get a list of country values for a product that don't exist in another table of "excluded" countries.
For example, my table of excluded values looks like:
LocationRestrictions
| productID | locationCode |
|---|---|
| 1 | ABC |
| 1 | DEF |
| 1 | GHI |
| 2 | ABC |
| 2 | GHI |
| 3 | JKL |
So basically all countries in Locations except any contained in LocationRestrictions table. The only issue is my Locations table does not have an ID column:
Locations
| locationCode |
|---|
| ABC |
| DEF |
| GHI |
| JKL |
| MNO |
| PQR |
| STU |
| VWX |
| YZ |
My current code looks like the following:
SELECT DISTINCT
productid,
locationcode,
Location
INTO #temp
FROM LabelRestrictions
WHERE productid = 1
SELECT locationcode, location
INTO #temp2
FROM Locations
SELECT DISTINCT
t2.location,
t2.locationcode
FROM #temp2 t2
WHERE t2.locationcode NOT IN (SELECT DISTINCT t1.locationcode FROM #temp t1)
Which returns something like:
| locationCode |
|---|
| JKL |
| MNO |
| PQR |
| STU |
| VWX |
| YZ |
But this only works if I specify the productid in the first SELECT statement and otherwise will return nothing.
What I'm looking for is something like:
| productID | locationCode |
|---|---|
| 1 | JKL |
| 1 | MNO |
| 1 | PQR |
| 1 | STU |
| 1 | VWX |
| 1 | YZ |
| 2 | DEF |
| 2 | JKL |
| 2 | MNO |
| 2 | PQR |
| 2 | STU |
| 2 | VWX |
| 2 | YZ |
| 3 | ABC |
| 3 | DEF |
| 3 | GHI |
| 3 | MNO |
| 3 | PQR |
| 3 | STU |
| 3 | VWX |
| 3 | YZ |
CodePudding user response：
You haven't said where you want the product list to come from. If you want all products by all locations you just need a cross join. If you want to filter the first half to a narrower set then that works too.
select productID, locationcode
from Products cross join Locations
except
select productID, locationcode
from LocationRestrictions