multi condition on different rows

age | name     | course | score
_________________________
10  |James    | Math   | 10
10  |James    | Lab    | 15
12  |Oliver   | Math   | 15
13  |William  | Lab    | 13 

I want select record where math >= 10 and lab >11 I write this query

select * from mytable
where (course='Math' and score>10) and  (course='Lab' and score>11) 

but this query does not return any record.

I want this result

age | name     
____________
10  |James   

where condition (math >= 10 and lab >11) is dynamically generate and perhaps has 2 condition or 100 or more…

please help me

Answer

If you want the names, then use aggregation and a having clause:

select name, age
from mytable
where (course = 'Math' and score > 10) or
      (course = 'Lab' and score > 11) 
group by name, age
having count(distinct course) = 2;

If you want the detailed records, use window functions:

select t.*
from (select t.*,
             (dense_rank() over (partition by name, age order by course asc) +
              dense_rank() over (partition by name, age order by course desc)
             ) as cnt_unique_courses
      from mytable t
      where (course = 'Math' and score > 10) or
            (course = 'Lab' and score > 11) 
     ) t
where cnt_unique_courses = 2;

SQL Server doesn’t support count(distinct) as a window function. But you can implement it by using dense_rank() twice.