How to do a sub-query in SQL

Question

My table looks something like this:

I want to retrieve all the PractitionerIdFK if they have SpecialityIdFK = 1 AND SpecialityIdFK= 2. I tried the following but it doesn't seem to work.

SELECT PractitionerSpecialities.PractitionerIdFK
FROM PractitionerSpecialities
WHERE PractitionerSpecialities.SpecialityIdFK IN (
        SELECT PractitionerSpecialities.SpecialityIdFK
        FROM PractitionerSpecialities
        WHERE PractitionerSpecialities.SpecialityIdFK = 1
            AND PractitionerSpecialities.SpecialityIdFK = 2
        )

Try or instead of and in your where clause since the SpecialityIdFK can't be both 1 and 2 at the same time. You could also do where PractitionerSpecialities.SpecialityIdFK IN (1, 2) — Mark M
– Mark M, Commented Jan 4, 2020 at 2:22
The subquery is unnecessary, Just change your IN expression as @MarkMoretto describes. — Nick
– Nick, Commented Jan 4, 2020 at 2:28

Gordon Linoff · Accepted Answer · 2020-01-04 03:23:56Z

1

You can use GROUP BY and HAVING:

SELECT ps.PractitionerIdFK 
FROM PractitionerSpecialities ps
WHERE ps.SpecialityIdFK IN (1, 2)
GROUP BY ps.PractitionerIdFK 
HAVING COUNT(*) = 2;  -- the size of the comparison list

This assumes that there are no duplicates in PractitionerSpecialities. If that is a possibility, then use HAVING COUNT(DISTINCT ps.SpecialityIdFK) = 2.

answered Jan 4, 2020 at 3:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Ajeet Verma Over a year ago

,In the question he\she doesn't ask about duplicate ,as per him he just want all PractitionerIdFK values for the SpecialityIdFK value 1 and 2

Ajeet Verma · Accepted Answer · 2020-01-04 02:52:25Z

1

It can be achieved by using IN and BETWEEN operator in SQL .

SELECT  PractitionerSpecialities.PractitionerIdFK 
FROM PractitionerSpecialities
WHERE PractitionerSpecialities.SpecialityIdFK in (1,2)

-- You can BETWEEN Clause as well ..

SELECT  PractitionerSpecialities.PractitionerIdFK 
FROM PractitionerSpecialities
WHERE PractitionerSpecialities.SpecialityIdFK BETWEEN 1 AND 2

In Sub query use OR operator instead of AND .

answered Jan 4, 2020 at 2:52

Ajeet Verma

1,1231 gold badge9 silver badges27 bronze badges

2 Comments

HJ1990 Over a year ago

Maybe I am a little bit confused but what if I want to return ONLY the PractitionerIdFK if they have SpecialityIdFK 1 and 3? I don't want to display users who have SpecialityIdFK 1 or 3 but instead return users who meet the criteria of having SpecialityIdFK 1 and 3?

Gordon Linoff Over a year ago

(1) I am baffled at what BETWEEN has to do with the question. (2) An upvote?

Collectives™ on Stack Overflow

How to do a sub-query in SQL

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related