need help with a query
Can anybody help with this query? It should be like this -
SELECT * FROM book WHERE supplierID=2 AND quantityInStock>0 ,
but the record should also be included in the result set if the table has a record with the same isbn and supplierID=9. How do I go about this?
(Note to self: next time when designing a database from scratch, think really good, so that you won't have to build lame queries 4 years later.)
is this what you mean?
SELECT * FROM book WHERE (supplierID=2 OR supplerID=9) AND quantityInStock>0
Not quite, I don't need records with supplierID=9 selected. I need all records with supplierID=2 where either quantityInStock>0 , or isbn equals isbn of another record in the same table where quantityInStock>0 and supplierID=9.
Say, if I have 2 records:
isbn supplierID quantityInStock
1888722010 2 0
1888722010 9 5
I would need the first record selected even though quantity=0 (don't need the second record)
Not sure exactly what you are after but I will give an example that may or may not help you solve it:
....sort of thing, you may want to play around with the join type
FROM book as b1, book as b2
and (b1.InStock>0 or b2.InStock>0)
and (b1.supplierID=2 or b2.supplerID=9;
Thanks a bunch for the help, I will be able to figure out what I need from your sample.