CODEWITHSUNDEEP
sql
ghostrider
ghostrider

Reputation: 5259

SQL getting rows that match my condition

I have a simple SQL table that has the following :

+---------------------+
| id | mode   | value |
+---------------------+
|  1 | EXCLUDE |  10  |
|  2 | INCLUDE |  10  |
|  3 | EXCLUDE |  10  |
|  3 | EXCLUDE |  20  |
+---------------------+

So imagine these are products. Product 1, has all values but 10. Product 2 has value 10 only. Product 3 has all values but 10 and 20.

I need an sql that given a value, I should return all products that this value is valid.

e.g. a value of 10 should return product 2. a value of 30 should return product 1 and 3.

Tried this :

SELECT * 
FROM products 
WHERE  ( mode = 'INCLUDE' AND value IN( 10) ) OR 
       ( mode = 'EXCLUDE' AND value NOT IN ( 10 ) )

for value 10 for example, but it also return the 4th row, which is expected based on the sql. How can i fix it? I want to group somehow the exclude ones based on the id and check values inside that ?

Upvotes: 1

Views: 41

Answers (3)

SqlZim
SqlZim

Reputation: 38023

Using not exists()

select distinct p.id
from products p
where (mode = 'INCLUDE' and value in (30))
  or (
    not exists (
      select 1 
      from products i
      where i.id = p.id 
        and mode = 'EXCLUDE' 
        and value in (30)
      )
    and exists (
      select 1 
      from products i
      where i.id = p.id 
        and mode = 'EXCLUDE' 
        and value not in (30)
      )
    )

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your problem is that ids are on multiple lines. So, think group by, with the conditions in having:

SELECT p.id
FROM products p
GROUP BY p.id
HAVING SUM(CASE WHEN mode = 'INCLUDE' and VALUE IN (10) THEN 1 ELSE 0 END) > 0 OR
       (SUM(CASE WHEN mode = 'EXCLUDE' THEN 1 ELSE 0 END) > 0 AND -- has excludes 
        SUM(CASE WHEN mode = 'EXCLUDE' and VALUE IN (10) THEN 1 ELSE 0 END) = 0 -- but not this one
       );

This is complicated logic. I think the logic is:

  • Keep the id if any id explicitly includes the value.
  • Remove the id if any id explicitly excludes the value.
  • Keep the id if any row excludes other values, but not this value.

Upvotes: 2

MBurnham
MBurnham

Reputation: 381

SELECT DISTINCT
    p.id
FROM
    products p
WHERE
    EXISTS ( SELECT 1 FROM products WHERE id = p.id AND value = 10 AND mode = 'INCLUDE' )
    OR 
    NOT EXISTS ( SELECT 1 FROM products WHERE id = p.id AND value = 10 AND mode = 'EXCLUDE')

Upvotes: 2

Related Questions