SQL

I was wondering you could tell me why my SQL is not working correctly.. I need it to query from the database the chemists that deliver to a certain area, but then from those only those of the Soul Pattinson chain...  

Here are my queries with results.:  

sql = "SELECT * FROM phoenix.ECShop WHERE DeliveryPostCodes LIKE '%," & CStr(intPCodeID) & ",%' OR DeliveryPostCodes = '" & CStr(intPCodeID) & "' OR DeliveryAreas2 LIKE '%," & CStr(intPCodeID) & ",%' OR DeliveryAreas2 = '" & CStr(intPCodeID) & "' AND BillNotes LIKE '" & strBrand & "' ORDER BY RestaurantName ASC" This shows all the chemists in the right area, not just the SP ones.  

sql = "SELECT * FROM phoenix.ECShop WHERE EXISTS (SELECT * FROM phoenix.ECShop WHERE BillNotes LIKE '" & strBrand & "') AND
(DeliveryPostCodes LIKE '%," & CStr(intPCodeID) & ",%' OR
DeliveryPostCodes = '" & CStr(intPCodeID) & "' OR DeliveryAreas2 LIKE '%," & CStr(intPCodeID) & ",%' OR DeliveryAreas2 = '" & CStr(intPCodeID) & "') ORDER BY RestaurantName ASC" This does exactly the same.  

sql = "SELECT * FROM phoenix.ECShop WHERE (DeliveryPostCodes LIKE '%," & CStr(intPCodeID) & ",%' OR DeliveryPostCodes = '" & CStr(intPCodeID) & "' OR DeliveryAreas2 LIKE '%," & CStr(intPCodeID) & ",%' OR DeliveryAreas2 = '" & CStr(intPCodeID) & "') AND RestaurantName IN
(SELECT * FROM phoenix.ECShop WHERE BillNotes LIKE '" & strBrand & "')
ORDER BY RestaurantName ASC"
Microsoft][ODBC SQL Server Driver][SQL Server]Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.  

sql = "SELECT * FROM phoenix.ECShop WHERE EXISTS (SELECT * FROM phoenix.ECShop WHERE DeliveryPostCodes LIKE '%," & CStr(intPCodeID) & ",%' OR DeliveryPostCodes = '" & CStr(intPCodeID) & "' OR DeliveryAreas2 LIKE '%," & CStr(intPCodeID) & ",%' OR DeliveryAreas2 = '" & CStr(intPCodeID) & "') AND (BillNotes LIKE '" & strBrand & "') ORDER BY RestaurantName ASC"
SHOWS ALL SP CHEMISTS     So I cant get any variations to show just the SP chemists (named here as a Restaurant) in an area..

Any help is much appreciated.
Girish Received on Thu Jan 31 2002 - 03:08:36 CET