How to filter conditioned on multiple rows in sql

user_9783433 0
Viewed 223

I'm looking for a way to filter over two row conditions. In general in basic sql it's possible to use the where and or statements to filter the query. What if i want to filter over two rows. I want to find every patient in following example, which belongs to both of two groups.

If i use:

select *  from table
where group = group1 and group = group2

obviously it's not working.

How can i query "find every patient with two specific group values"? In this example i'm looking for group1 and group2, so peter should not be matched, though he is in two groups. 

 Student
    id | patient | group
    1  | jhon    | group1
    2  | jack    | group1
    3  | jill    | group2
    4  | jack    | group2
    5  | jill    | group1
    6  | peter   | group2
    7  | peter   | group3

Expected output (ordering not necessary):

 Student
    id | patient | group
    1  | jack    | group1
    2  | jack    | group2
    3  | jill    | group1
    4  | jill    | group2

It was mentioned, using "group" as column name is bad, that's true. I wanted to point this out, since i don't want to change the question.

relational-division sql
3 Answers
SELECT * 
FROM Student 
WHERE patient IN (
    SELECT s1.patient 
    FROM Student s1
    INNER JOIN Student s2
    ON s2.patient = s1.patient
    AND s2.group = 'group2'
    WHERE s1.group = 'group1'
    )
user_52569450

Use IN oeperator with count distinct :

select * 
from student 
where patient in (select patient
                  from student
                  where group_c in ('group1', 'group2')
                  group by patient
                  having count(distinct group_c) = 2)

Here is a demo for SQLServer

P.S. I have changed name of the column group into group_c because it is not a good practice to use keywords in your column names.

user_65650380 
SELECT
 *
FROM (SELECT 
        patient
      FROM Student 
      WHERE group = 'group1' OR group = 'group2'
      GROUP BY patient
      HAVING COUNT(*) = 2) A
INNER JOIN Student ON Student.patient = A.patient

The second select is for getting the rest of the info. If patient name is enough for you then the inner query will be enough. Notice that where filters before having.

user_98224010
Related