mlg mlg - 1 year ago 74
SQL Question

Filter data based on multiple rows SQL

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

--- ------ -------
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

--- ------ -------
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 Source

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 =
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download