CODEWITHSUNDEEP
selectmysqlnested
klay
klay

Reputation: 23

nested sql statement using and

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

Upvotes: 0

Views: 89

Answers (2)

Tomalak
Tomalak

Reputation: 338228

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.

Upvotes: 1

Lars Andren
Lars Andren

Reputation: 8781

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.

Upvotes: 0

Related Questions