CODEWITHSUNDEEP
sqlmysql
payling
payling

Reputation: 2524

mysql query excluding a set of data but includindg certain items from the set if item contains value in a specific column

I need to exclude a product line from a database so I do pline != prodctline but certain products in that product line I do not want to be excluded. For instance: here is simlplistic version of my database:

partname, pline, option (3 columns)

i want to exclude all results from a certain product line unless they have X option value. soo

part name, product line, option

widget1 10 null

widget2 10 option1

widget3 10 option5

widget4 10 option2

so i might want to exclude all of product line 10 unless it comes with option 1.

or

i might want to exclude all of product line 10 but unless it comes with option 1 and option 2.

Hope this helps

I'm not sure how to go about doing this in a query. Is there a sql clause that I'm not aware of?

I was trying to do it with where pline != 90 and option = 'option1' but that was not working.

Thanks

Upvotes: 1

Views: 1446

Answers (1)

Adrian Dunston
Adrian Dunston

Reputation: 2940

Actual Answer

I want all info about all products that have a product line other than 10. I also want the ones that have a product line of 10 but also have an option of "option1" or "option2". Here's how I express that:

select part_name, product_line, option 
from products 
where product_line <> 10 or option = "option1" or option = "option2";

The <> means not equal to. I say it's not equal to 10 or it's option 1 or it's option 2. If a row matches any of this criteria, it's accepted.

One step further...

Let's try something more complicated. Let's say you want all products with a product line less than 10. But you also want all the products with a line of exactly 10 (not 11 or 12, etc) that have an option of "option1" or "option2". In this example, the fact that the product line = 10 becomes important for option1 and option2.

select part_name, product_line, option
from products
where 
  product_line < 10 
  or 
  (product_line = 10 and (option = "option1" or option = "option2"));

With a combination of ands, ors, and parentheses, you can be very specific about what you are selecting from the database.

Upvotes: 2

Related Questions