Help with multiple tables SQL

[Chris05]

Hi hope someone can help.

I have 3 tables, a location table (showing the id and name of each location), a product table (showing the id and name of each product) and a storeProducts table, showing which stores sell which products. I want to be able to display a list of all locations, plus another column (true/false) stating whether they appear in the storeProducts table for a specified product. I can obviously get a list of locations that appear in that table for product, say, 'p6':

SELECT location.locationID, location.locationName
FROM location INNER JOIN storeProducts ON location.locationID = storeProducts.locationID
WHERE (((storeProducts.productID)='p6'));

But I would like to display all the locations, and state whether they appear in the storeProducts table for product'p6'. Can anyone help?

[MMcCarthy]

SELECT location.locationID, location.locationName,
If (storeProducts.productID='p6') Then 'Yes' Else 'No' As InStore
FROM location LEFT JOIN storeProducts 
ON location.locationID = storeProducts.locationID
WHERE (((storeProducts.productID)='p6'));
 

Not 100% sure if the IF...ELSE syntax is correct for MySQL but worth a try.

Mary