CODEWITHSUNDEEP
sql-serversql-server-2005conditional-statementssubquery
Steph Rose
Steph Rose

Reputation: 2136

SQL Subquery Select Table based on Outer Query

I have a general query that looks like this:

SELECT DISTINCT pb.id, pb.last, pb.first, pb.middle, pb.sex, pb.phone, pb.type, 
specialties = substring(
    SELECT ('|' + cs.specialty )
    FROM CertSpecialty AS cs 
    INNER JOIN CertSpecialtyIndex AS csi on cs.specialty = csi.specialty
    WHERE cs.id = pb.id
    ORDER BY cs.sequence_no
    FOR XML path(''),2,500)
FROM table AS pb 
WHERE etc etc etc

The issue is this:

The "type" column that I'm selecting is an integer - types 1-4.

In the subquery, see where I am querying from the table CertSpecialty right now.

What I actually need to do is, if the type field comes back as a 1 or a 3, that's the table I need to query. But if the row's result is a type 2 or 4 (i.e., an ELSE), I need to be querying the same column in the table CertSpecialtyOther.

So it'd need to look something like this (though this obv doesn't work):

SELECT DISTINCT pb.id, pb.last, pb.first, pb.middle, pb.sex, pb.phone, pb.type, 
specialties = 
IF type in (1,3)

    substring((SELECT ('|' + cs.specialty )
    FROM CertSpecialty AS cs 
    INNER JOIN CertSpecialtyIndex AS csi on cs.specialty = csi.specialty
    WHERE cs.id = pb.id
    ORDER BY cs.sequence_no
    FOR XML path(''),2,500)

ELSE

    substring((SELECT ('|' + cs.specialty )
    FROM CertSpecialtyOther AS cs 
    INNER JOIN CertSpecialtyIndex AS csi on cs.specialty = csi.specialty
    WHERE cs.id = pb.id
    ORDER BY cs.sequence_no
    FOR XML path(''),2,500)    

end
FROM table AS pb
WHERE etc etc etc

Is this possible? If so, what is the correct syntax? Is there a simpler way to write it where I'm switching which table I query without completely duplicating the subquery?

Also, does anyone have a good resource they could link me for this sort of thing to learn more besides?

Thanks in advance.

Upvotes: 1

Views: 688

Answers (1)

Anthony Faull
Anthony Faull

Reputation: 17957

Use a CTE.

;WITH cs AS
(
    SELECT 'A' SpecialtyCategory, phy_key, specialty
    FROM CertSpecialty

    UNION ALL

    SELECT 'B' SpecialtyCategory, phy_key, specialty
    FROM CertSpecialtyOther
)
SELECT csi.id, cs.specialty
FROM cs 
INNER JOIN CertSpecialtyIndex AS csi on cs.specialty = csi.specialty
WHERE cs.phy_key = pb.phy_key
AND cs.SpecialtyCategory = (CASE WHEN type in (1,3) THEN 'A' ELSE 'B' END)

Upvotes: 1

Related Questions