0

I need a query to select all rows where it selects all rows based on multiple product ids in products and exclude products with ids as well and include the blank ones as well.

See the image for more details enter image description here

I have written a query but the blank ones are getting ignored.

select  
  from discounts 
 where (
         FIND_IN_SET('52238403',products) 
      or FIND_IN_SET('52238407',products) 
      or FIND_IN_SET('52238408',products)
       ) 
     and 
       (
         FIND_IN_SET('52238403',exclude_products)=0 
     and FIND_IN_SET('52238407',exclude_products)=0 
     and FIND_IN_SET('52238408',exclude_products)=0
       )

Any help is much appreciated.

Thanks

user399883
  • 243
  • 1
  • 3
  • 11
  • I see you dont like to accept answers any reason for that? – Mihai Feb 17 '17 at 09:43
  • Any time you find yourself using FIND_IN_SET, you can be sure that something has gone very, very wrong with your data model. – Strawberry Feb 17 '17 at 09:45
  • @Mihai - sorry i completely forgot to upvote answers for a while, but i will do it definitely. thanks for pointing out. – user399883 Feb 17 '17 at 09:47
  • @Strawberry Ok if that's the case i will make it simple so you can let me know the workaround. – user399883 Feb 17 '17 at 09:48
  • 1
    See normalisation. – Strawberry Feb 17 '17 at 09:51
  • Presumably the "blank" ones have null values - try ifnull(exlude_products,0) – P.Salmon Feb 17 '17 at 10:48
  • "i will do it definitely" Great. Let us know when you have. ;-) – Strawberry Feb 17 '17 at 11:04
  • @Strawberry normalization helps but how can i find rows which has empty value in join statement. – user399883 Feb 17 '17 at 11:19
  • 1
    You'd typically use an OUTER JOIN and select NULL results – Strawberry Feb 17 '17 at 11:32
  • @Strawberry sorry to be a pain, I have updated the image to make it clear of the select query. Basically i want to filter the rows based on the condition i.e we have subtotal which should be greater than minimum amount and less than maximum amount and the product Ids can be null or it can be present in products or exclude products etc. So is it a good idea to shift to normalization so we create 2 more tables for products and exclude products or there is a simplest way of achieving it. I just need to know i am adopting the right database structure. – user399883 Feb 17 '17 at 11:49
  • No pictures, thanks. See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query . Normalization isn't simply 'a good idea'. It is the fundamental principle of RDBMSs. If it's not normalized (at least to 3NF) then it isn't really a database (in the RDBMS sense of the word, anyway) – Strawberry Feb 17 '17 at 11:55
  • Separate blacklists and whitelists seems like a reasonable approach - the only thing you have to be aware of is the possibility of an item accidentally appearing simultaneously in both tables. It *might* make more sense to have a single table which records whether a given item is blacklisted or whitelisted - or assume that all products are whitelisted (or blacklisted) unless otherwise specified. – Strawberry Feb 17 '17 at 11:59

0 Answers0