2

I have two tables, stock and location, with a third table that retains the level of stock at each location, stock_loc_info.

Each stock item should have a row for each location.

What SQL query would show that the row B1 is missing from stock_loc_info?

stock.stockcode
---------------
A
B

location.locno
--------------
1
2

stock_loc_info.fkstockcode  stock_loc_info.fklocno
--------------------------  ----------------------
A                           1
A                           2
B                           2
3

You need all combinations from the two tables, so a CROSS JOIN and then remove the ones in the 3rd table, using either NOT EXISTS or EXCEPT:

SELECT 
    s.stockcode, l.locno
FROM
    dbo.stock AS s
  CROSS JOIN
    dbo.location AS l

EXCEPT

SELECT 
    si.fkstockcode, si.fklocno
FROM 
    dbo.stock_loc_info AS si ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.