nested sql statment using and

Question

hi guys, how to make this work in mysql?

select ID,COMPANY_NAME,contact1, SUBURB, CATEGORY, PHONE from Victoria where (city in ( select suburb from allsuburbs)) and CATEGORY='Banks'

this below statement is working: select ID,COMPANY_NAME,contact1, SUBURB, CATEGORY, PHONE from Victoria where city in ( select suburb from allsuburbs)

if I add "and" , it gives me an empty resultset, thanks

Answer 1

I think you might have misplaced a parentheses?

.. PHONE from Victoria where 
(city in ( select suburb from allsuburbs)) and CATEGORY='Banks'

I'm guessing should be:

.. PHONE from Victoria where 
city in ( select suburb from allsuburbs) and CATEGORY='Banks'

Not sure if that makes more sense, but the first case is not an ok SQL-statement I believe.

Answer 2

Learn how joins work.

select 
  v.ID,v.COMPANY_NAME,v.contact1,v.SUBURB,v.CATEGORY,v.PHONE 
from
  Victoria v
  inner join allsuburbs s on s.suburb = v.city 
where 
  v.CATEGORY='Banks'

Apart from that, your query does not make a whole lot of sense.

• Your table is namend Victoria, but it contains a field named city?! Do your other cities have their own table too?
• You have a table named allsuburbs, but your criterion is that Victoria.city equals allsuburbs.suburb, even though a field named Victoria.suburb exists?! What's Victoria.suburb for, then?
• Your table is named allsuburbs. Do you have another table that contains suburbs or is this your only one? If it is your only one, the name is redundant.
• You have a field contact1. Do you have contact2...contact10 as well? Bad database design.
• Why is half of your fieldnames in caps, and not all of them (or none of them)?

Oh, and the usual format for SQL is: SQL keywords in caps, the field names etc. in mixed case/lower case. Much easier to read.