CODEWITHSUNDEEP
sqlpostgresql
user2954587
user2954587

Reputation: 4861

SQL join subquery where condition

How can I effectively subquery a LEFT OUTER JOIN so that only rows that meet a specific condition in the join are included?

I'd like to only count PPPD's where converted_at IS NULL. However when I add PPPD.converted_at IS NULL, then the result is more limited than I'd like it to be because it only includes patient_profiles that do have a row with null in converted_at. Instead I'd like a count of all PPPD records that have converted_at = null 

SELECT P.id, P.gender, P.dob, 
count(distinct recommendations.id) AS recommendation_count,
count(distinct PPPD.id) AS community_submissions,
FROM patient_profiles AS P
LEFT OUTER JOIN recommendations ON recommendations.patient_profile_id = P.id
LEFT OUTER JOIN patient_profile_potential_doctors AS PPPD ON PPPD.patient_profile_id = P.id
WHERE P.is_test = FALSE
GROUP BY P.id

Upvotes: 1

Views: 993

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You need to add the condition in the ON clause:

SELECT P.id, P.gender, P.dob, 
       count(distinct r.id) AS recommendation_count,
       count(distinct PPPD.id) AS community_submissions,
FROM patient_profiles  P LEFT OUTER JOIN
     recommendations r
     ON r.patient_profile_id = P.id LEFT OUTER JOIN
     patient_profile_potential_doctors PPPD
     ON PPPD.patient_profile_id = P.id AND PPPD.converted_at IS NULL
WHERE P.is_test = FALSE;
GROUP BY P.id

Upvotes: 2

Related Questions