Filter data based on multiple rows SQL

Question

This is probably as simple SQL query. I'm finding it little tricky, as it's been a while I've written SQL.

ID  NAME        VALUE
--- ------      -------
1   Country     Brazil
1   Country     India
2   Country     US
2   EmpLevel    1
3   EmpLevel    3

Pseudo Query:

Select * 
from table_name 
where (country = US or country = Brazil) 
  and (Employee_level = 1 or Employee_level = 3)

This query should return

 ID NAME        VALUE
 ---    ------      -------
 2      Country      US
 2      EmpLevel     1

(As record with ID - 2 has Country as 'US' and EmpLevel '1')

I went through couple SO posts as well.

Multiple row SQL Where clause

SQL subselect filtering based on multiple sub-rows

Evaluation of multiples 'IN' Expressions in 'WHERE' clauses in mysql

Answer 1

I assume you're expected results for the country should be US instead of Brazil. Here's one option using a join with conditional aggregation:

select y.* 
from yourtable y join (
  select id
  from yourtable
  group by id
  having max(case when name = 'Country' then value end) in ('US','Brazil') and
         max(case when name = 'EmpLevel' then value end) in ('1','3')
) y2 on y.id = y2.id

• SQL Fiddle Demo