Re: SQL

You could try to format it a little bit so it is readable. Also remove all the VB code we really don't need to see that. Jim
"Girish Mohata" <gmohata_at_yahoo.com> wrote in message news:42a840c2.0201301808.79b9661f_at_posting.google.com...
> 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 - 15:04:21 CET