CODEWITHSUNDEEP
sqloracle-database
priya
priya

Reputation: 375

multiple where clauses

I have the following table:

 ID    Key   Value
 1     From  x
 1     To    Y
 1     CC    a
 2     From  Z
 2     To    X
 2     CC    b
 3     From  X
 3     To    Y  
 3     CC    c
 4     From  X
 4     To    Z
 4     CC    d

I want Id 1 and 3 as result such that where key =" From " and value = "x " and where key =" To " and value = "Y " (From->To should be x->y) It should be something like this 

SELECT Id
FROM table_name
WHERE REGEXP_LIKE(KEY, '(^|\s)from(\s|$)', 'i')
    AND REGEXP_LIKE(value, '(^|\s)x(\s|$)', 'i')
    AND
WHERE
WHERE REGEXP_LIKE(KEY, '(^|\s)to(\s|$)', 'i')
    AND REGEXP_LIKE(value, '(^|\s)y(\s|$)', 'i')

Is it possible to solve this problem?

Upvotes: 2

Views: 74

Answers (2)

Rabbit
Rabbit

Reputation: 507

Join the table to itself and filter accordingly.

    SELECT ID
    FROM 
            Table t1
            INNER JOIN Table t2
            ON t1.ID = t2.ID
    WHERE
            t1.Key = 'From' AND
            t1.Value = 'x' AND
            t2.Key = 'To' AND
            t2.Value = 'y'

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Using conditional SUM

SqlFiddleDemo

SELECT ID
FROM Table1
GROUP BY ID
HAVING 
      SUM(CASE WHEN "Key" = 'From' and LOWER("Value") = 'x' THEN 1 ELSE 0 END) = 1 
 AND  SUM(CASE WHEN "Key" = 'To' and LOWER("Value") = 'y' THEN 1 ELSE 0 END) = 1

OUTPUT

| ID |
|----|
|  1 |
|  3 |

Upvotes: 2

Related Questions