SQL - Finding same product in different waehouse where price is not the same

Richard Berry100

Hi I need to run a query on a stock table. Each product may exist in either one or many warehouses. I am trying to find all items where the cost of the product is not the same in warehouse 01 and 02. Example Wh Prod Cost 01 A 10.00 02 A 10.00 01 B 64.00 01 C 98.00 02 C 10.00 In the example data above, I would like to get the last two rows returned. This is what I have tried, but get lots of the same product code

SELECT stockm.warehouse, stockm.product, stockm.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B,
scheme_stockm AS stockm
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY stockm.product, stockm.warehouse

David Mujica

I think you are close ... but try this:

SELECT A.warehouse, A.product, A.standard_material,B.warehouse, B.product, B.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY A.product, A.warehouse

I believe you included the scheme_stockm too many times in your original query.

Richard Berry100

Thanks David I had to add warehouse in the two select statements for warehouse '01' and warehouse '02' since they were referred to in the outer SELECT as below and now it works.

SELECT A.warehouse, A.product, A.standard_material,B.warehouse, B.product, B.standard_material FROM
(SELECT warehouse, product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT warehouse, product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY A.product, A.warehouse

I also got mine to work by adding an additional 'join' as below on stockm.product = A.product, but I prefer yours, since the result is one record per error, whereas mine is two records per error

SELECT stockm.warehouse, stockm.product, stockm.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B,
scheme_stockm AS stockm
WHERE (A.product = B.product AND A.standard_material <> B.standard_material AND stockm.product = A.product)
ORDER BY stockm.product, stockm.warehouse

David Mujica

Glad it worked ouy for you. :thumbsup:

Michael Potter

Your query will only work if you have 2 warehouses. What happens if there are 3 or more? Try:

SELECT
a.warehouse,
a.product,
a.standard_material
FROM
scheme_stockm AS a
WHERE
EXISTS (SELECT *
FROM scheme_stockm
WHERE product = a.product AND
standard_material <> a.standard_material)
ORDER BY
a.product,
a.warehouse

Richard Berry100

Hi Michael I had wondered about multiple warehouses :) In this particular case the product can exist in other warehouses, however the cost in the others does not need to be the same - only warehouse 01 and 02 must be the same. I did test your query, and it works perfectly! Thanks as I'm sure I'll need something like this in the future