SQL multiple join with count, having trouble.

Question

Hello All, First of all this is a homework assignment so I'm looking for assistance, not solutions. Let me try to explain my schema. I have three tables we'll call users (with columns id and name), parties (with columns id, partydate, and user_id) and questions (with columns id, createdate, and user_id). My requirement is to show for every user the number of parties within the last year and questions created within the last year. At first I had something like this:

SELECT users.id, users.name,  
COUNT(parties.id) AS numparties, COUNT(qustions.id) AS numquestions
FROM users
FULL JOIN parties ON users.id=parties.user_id
FULL JOIN questions ON users.id=questions.user_id
WHERE (parties.partydate > NOW() - interval '1 year' OR parties.partydate IS NULL)
OR (questions.createdate > NOW() - interval '1 year' OR questions.createdate IS NULL)
GROUP BY users.id, users.name

Now this works, almost! The problem is, if a user has no parties nor questions within the past year, they don't show up at all in the result. I want such a user to show up, I just want it to show them with 0 for each numparties and numquestions.

What I think I need here is some sort of conditional counting, where I only want to COUNT(parties.id) WHERE that party's partydate is within the past year, and the same for questions. I'm just unsure how to do that. I have a hacky-workaround way to do what I want, where I basically UNION the above query with a near identical copy of itself, except I use SUM(0) for numparties and numquestions and my WHERE statement is just where the date is <= instead of >. I feel this is not the best way to go about it.

Any pointers in the right direction? Thanks for the help!

Answer 1

Take a look at this: http://www.w3schools.com/sql/sql_join_left.asp. I think it might point you in the right direction.

Answer 2

Think I've got it with this:

SUM(CASE WHEN (parties.partydate > NOW() - interval '1 year') THEN 1 ELSE 0 END) as numparties

and just removed the WHERE clauses.

Answer 3

I think I'd resort to a subquery for this. Homework questions are fun to answer, I can heavily psuedo code this. Take the entire query you have and call it 'x'.

First thing you'll want is a list of all users regardless of how many questions they've asked.

Select distinct users.id,users.name from users

that will give you a full list of your users. The query you have above gives you there calls...so left join the two together.

Select (fields you want)
from users
left join (enter you query above here) x on x.id = users.id

Hopefully the logic here makes sense for you. Use one query to get the list of users and join that to the subquery to get their counts.

edit to add: this will bring back nulls anytime there are no records. You can make your select statement show nulls as 0's