SQL multiple table join subquery

Question

How can I use a sub query for just the invite table? I'd like all records from patient_profiles and for the invites join to use only records created after a specific date?

SELECT p.first_name,
COUNT(invites.invited_by_id)as invite_count
FROM patient_profiles AS p
LEFT OUTER JOIN patient_profiles AS invites ON invites.invited_by_id = p.id
WHERE p.is_test = false AND AND invites.created_at >= '2017-10-16'::date 
GROUP BY p.first_name

Gordon Linoff · Accepted Answer · 2017-10-18 15:09:52Z

1

You don't need a subquery. Just move the date condition to the ON clause:

SELECT p.first_name,
       COUNT(invites.invited_by_id)as invite_count
FROM patient_profiles p LEFT OUTER JOIN
     patient_profiles invite
     ON invites.invited_by_id = p.id AND invites.created_at >= '2017-10-16'::date
WHERE p.is_test = false
GROUP BY p.first_name;

answered Oct 18, 2017 at 15:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Matt · Accepted Answer · 2017-10-18 14:04:45Z

0

You can use HAVING.

SELECT p.first_name, COUNT(invites.invited_by_id)as invite_count
FROM patient_profiles p
GROUP BY p.first_name
HAVING p.is_test = false AND p.created_at >= '2017-10-16'::date

answered Oct 18, 2017 at 14:04

Matt

15.1k28 gold badges109 silver badges161 bronze badges

1 Comment

user2954587 Over a year ago

this appears to filter all records based on the date, I'd only like the records for COUNT() to be filtered based on the date clause

Collectives™ on Stack Overflow

SQL multiple table join subquery

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related