Join query result of 2 different tables based on common column

Question

I want to join the result of two different queries from two different tables based on a common column (which is named differently in the two tables) but I cant seem to figure out the easy way to do it. I'm using psycopg2 on python 3.5.

From the first table, I need the Count of meetings. So I do:

SELECT user_id,COUNT(1) as num
FROM tasks
WHERE created_at BETWEEN %s and %s
GROUP BY user_id """,(DAYS_AGO_7_CONFIG,YESTERDAY))

This gives me

UserID | Number
1 | 123
2 | 12

UserID is my mutual column. Now I made userID into a list by list comprehension, then I do:

SELECT id, given_name,family_name,email FROM users WHERE id in +str(userID))

and I get the id, given_name, family_name, and email fields from the second table.

Now I need to add the Number column into this result, which I'm doing by very inefficient non-DB means (pandas library in python)

I'm pretty sure this entire thing can be done in one query (perhaps with union?), but I cant seem to work it out.

Intended output:

id | given_name | family_name | email | Number

• Andriy M: Please show what the output should look like, as that's not entirely clear from your description.
• Wboy: Added, but i was using a guest account hence now it's pending peer edit
• Andriy M: Feel free to request an account merge for your two accounts so that you can restore your full control over your question.

Answer 1

You can do it with:

SELECT u.id, u.given_name, u.family_name, u.email, COUNT(1) as num
FROM users u JOIN tasks t
 ON (u.id=t.user_id)
WHERE t.created_at BETWEEN %s and %s
GROUP BY u.id, u.given_name, u.family_name, u.email, (DAYS_AGO_7_CONFIG,YESTERDAY))

To address the question in the comments, you can add a HAVING clause after GROUP BY to limit the results to those where num is greater than 5:

HAVING COUNT(1)>5

• Wboy: THANK YOU! i would mark you correct, but i was using a guest account just now =/ The WHERE clause in the 3rd line was unneeded as the ON did that, but is there a way to filter based on the num being > 5? i get num does not exist
• Marco: @Wboy Changed my answer.