CODEWITHSUNDEEP
sqlsql-server-2008
fr21
fr21

Reputation: 1816

where condition multiple values on multiple columns

create table testCondition(obj int, prop int, val nvarchar(255));
Go
insert into testCondition values(1, 10, 'A')
insert into testCondition values(1, 11, 'B')
insert into testCondition values(1, 12, 'C')
insert into testCondition values(2, 10, 'E')
insert into testCondition values(2, 11, 'B')
insert into testCondition values(2, 12, 'C')

This table holds 2M+ such records. How to list all objects where (prop = 10 and val = 'A') and (prop = 12 and val = 'C')?

Expected output:

1, 10, 'A'
1, 12, 'C'

Upvotes: 0

Views: 55

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460108

Use OR

...
WHERE (prop = 10 and val = 'A')
  OR  (prop = 12 and val = 'C')

Edit: now i understand, you want all rows for a given ID where both conditions are true. That's also the reason why you omit 2, 12, 'C' since 2 does not have a row with prop = 10 and val = 'A'.

Then i would use EXISTS:

SELECT * FROM testCondition tc
WHERE (
  prop = 10 and val = 'A'
  AND EXISTS
  (
      SELECT 1 FROM testCondition tc2
      WHERE tc2.obj = tc.obj AND prop = 12 AND val = 'C'
  )
)
OR (
  prop = 12 and val = 'C'
  AND EXISTS
  (
      SELECT 1 FROM testCondition tc2
      WHERE tc2.obj = tc.obj AND prop = 10 AND val = 'A'
  )
)

Here's a demo: http://sqlfiddle.com/#!3/8fe0c/2/0

Upvotes: 1

SchmitzIT
SchmitzIT

Reputation: 9552

This should do the trick:

SELECT 
    a.obj, a.prop, a.val 
FROM
    testCondition a
    JOIN (SELECT obj FROM testCondition WHERE (prop = 10 AND val = 'A')) AS b ON a.obj = b.obj
    JOIN (SELECT obj FROM testCondition WHERE (prop = 12 AND val = 'C')) AS c ON a.obj = c.obj
WHERE 
  ((prop = 10 AND val = 'A') OR 
  (prop = 12 AND val = 'C'))

The two JOINS ensure that we only grab the obj-ids where both conditions are true. We then filter out the unwanted rows with a valid obj id in the WHERE clause.

Here's an example sqlfiddle.

Upvotes: 0

Related Questions