3

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.

mischva11
  • 2,811
  • 3
  • 18
  • 34

3 Answers3

2
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'
    )
Joan Lara
  • 1,362
  • 8
  • 15
  • Wont the inner select be sufficient? You have all the info – A_kat Feb 25 '20 at 08:48
  • @A_kat No, the inner will return jack and jill, if you want the IDs and the groups you have to do it this way. – Joan Lara Feb 25 '20 at 08:52
  • 1
    This solution will throw errors because you need to specify the alias wheer are you selecting patient from. – VBoka Feb 25 '20 at 09:37
  • 1
    @VBoka Yes, I added it. – Joan Lara Feb 25 '20 at 10:04
  • 1
    Nice, vote up from me because this will also return good results now that the error is removed: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4dadf8fbe70d81fd86a116f16b47df52 – VBoka Feb 25 '20 at 10:28
2

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.

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • 1
    That does not answer the question. – Joan Lara Feb 25 '20 at 08:39
  • 1
    Hi @JoanLaraGanau thanks for the info. It does now. – VBoka Feb 25 '20 at 09:40
  • 1
    Hi @mischva11 sorry for the stundent/student mix. I have changed it... I was just editing at the same time you were so I have run over your changes... – VBoka Feb 25 '20 at 09:44
  • sure, no problem. I like the solution with the distinct count, which seems inspired from @A_kat . Using a keyword is bad, that's true. Thanks for hinting that, still gonna keep the question worded like this. Otherwise it's getting confusing with existing answers. – mischva11 Feb 25 '20 at 09:52
  • 1
    Hi @mischva11 well it was not really inspired by that answer because if you look at it better not only that I have used IN operator instead of OR operator but also my result will return all columns from table student while that answer(if it was correct) would return only column patient. – VBoka Feb 25 '20 at 09:58
1
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.

A_kat
  • 1,459
  • 10
  • 17
  • 1
    But @mischva11 is this a good answer if for example peter has group1 and group1 ? I believe not ? – VBoka Feb 25 '20 at 09:25