mysql select with multiple "in"

Question

i need some help with select statement.

table: mobiles (brand,model,price)

i tried the below syntax to fetch the content.

select * 
from mobiles 
where brand in ('apple','nokia','samsung') 
      and model in ('lumia 510','galaxy s3')

the above query is showing only "nokia lumia 510" and "samsung galaxy s3".

i want it to display all models in 'apple', only 'lumia 510' from 'nokia' and 'galaxy s3' from 'samsung'.

am i wrong at using the syntax?

i am developing a site where users can filter mobiles by brand and model. i need to display only the mobiles what user's select. for each brand if the user select's specific model, then, only that model should be displayed, if he selects only the brand then all the mobiles under that brand should be displayed.

he can choose any number of brands, and under each brand he might or might not select the specific model.

i tried it. but its displaying all the models in nokia and samsung. — srujan reddy c
– srujan reddy c, Commented Feb 22, 2013 at 23:32

Barmar · Accepted Answer · 2013-02-22 23:32:44Z

1

You can't do this with IN, because each brand has different model requirements.

SELECT *
FROM mobiles
WHERE (brand = 'apple')
OR (brand = 'nokia' AND model = 'nokia lumia 510')
OR (brand = 'samsung' AND model = 'galaxy s3')

If models are always unique to a specific brand, you can simplify it a bit:

SELECT *
FROM mobiles
WHERE brand IN ('apple')
OR model IN ('nokia lumia 510', 'galaxy s3')

Put all the brands that have no model selected in the first IN clause, and all the models in the second one. The brand is implied by the model, because of the unique relationship.

answered Feb 22, 2013 at 23:32

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

srujan reddy c Over a year ago

wowww. thats an awesome solution. thanks for the quick reply.

srujan reddy c Over a year ago

i need to add one more condition to it. "and condition=used". its not working.

Barmar Over a year ago

Make sure you parenthesize properly. AND binds more tightly than OR. brand in X OR model in Y AND condition = Z is equivalent to brand in X OR (model in Y AND condition = Z).

Collectives™ on Stack Overflow

mysql select with multiple "in"

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related