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
)