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

sgeddes · Accepted Answer · 2016-08-19 04:24:42Z

4

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

answered Aug 19, 2016 at 4:24

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mlg Over a year ago

Bit late, :) Yes, Updated the question as per the comment

Collectives™ on Stack Overflow

Filter data based on multiple rows SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related