SQL statement question

[mds1256]

Hi

 

This has been driving me crazy, I have the following tables:

 

Parts:

Part_ID

p1
p2
p3
p4
p5

 

 

Store:

Store_ID

s1
s2
s3
s4

 

Stocks:

Part_ID | Store_ID
p1          s1
p2          s1
p3          s1
p4          s1
p5          s1
p1          s2
p2          s2

 

 

Any what I am trying to do is to get a list of ALL store_IDs which do not stock all products.

 

So I need to check inside the Stocks table to see if for each Store_ID that each instance of Product_ID exists for that Store.

 

Any ideas?

 

[DaveyK]

are p1, p2, p3 and such ids of rows or the names of columns?

[Psycho]

Maybe not the most efficient, but this works

 

This will return just the store IDs that do not carry all the parts

 

SELECT s.store_id
FROM store AS s
JOIN parts AS p
LEFT JOIN `stocks` AS st
   ON  s.store_id = st.store_id
  AND  p.part_id = st.part_id
WHERE st.store_id IS NULL
GROUP BY s.store_id

 

Or, if you want to include the list of parts each store does not carry, then include the part_id in the SELECT clause and remove the GROUP BY.

 

SELECT s.store_id, p.part_id
FROM store AS s
JOIN parts AS p
LEFT JOIN `stocks` AS st
   ON  s.store_id = st.store_id
  AND  p.part_id = st.part_id
WHERE st.store_id IS NULL
-- GROUP BY s.store_id

[mds1256]

Works Perfect!!!

 

Thank you very much