Skip to content
Advertisement

Multiple conditions on the same column in the WHERE clause

I have a table something like this –

RecordID   PropertyID       PropertyVal
--------------------------------------------------
3215            7           john doe
3215            11          Chicago
3215            13          Business Development Analyst
3216            7           jane doe
3216            11          Chicago
3216            13          Managing Director
3217            7           mike smith
3217            11          Chicago
3217            13          Business Development Analyst
3218            7           john smith
3218            11          Seattle
3218            13          Managing Director

How do I return the names of users where PropertyID = 13 AND PropertyVal='Business Development Analyst'AND PropertyID = 11 AND PropertyVal = 'Chicago'. How do I do multiple where clauses for the same column?

Edit: I need the result set to look like this –

Name
----
John Doe
Mike Smith

Advertisement

Answer

select PropertyVal
from your_table
where PropertyID = 7
and RecordID in 
(
  select RecordID   
  from your_table
  where (PropertyID = 13 AND PropertyVal='Business Development Analyst')
     or (PropertyID = 11 AND PropertyVal = 'Chicago')
  group by RecordID   
  having count(distinct PropertyID) = 2
)
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement