CODEWITHSUNDEEP
sql
HJ1990
HJ1990

Reputation: 415

How to do a sub-query in SQL

My table looks something like this:

SQL TABLE

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
        )

Upvotes: 0

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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.

Upvotes: 1

Ajeet Verma
Ajeet Verma

Reputation: 1123

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 .

Upvotes: 1

Related Questions