SQL multiple counter connection having problems

Question

First of all, this is homework, so I'm looking for help, not solutions. Let me try to explain my scheme. I have three tables that we will call users (with identifiers and column names), parties (with column identifiers, partydate and user_id) and questions (with column identifiers, createate and user_id). My requirement is to show each user the number of parties over the past year and the questions created over the past 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 it works, almost! The problem is that if the user has no parties and questions during the past year, they do not appear at all as a result. I want this user to be displayed, I just want him to show them with 0 for each numparties and numquestions.

It seems to me that I need some kind of conditional calculation here, where I want only COUNT (parties.id) WHERE that the partydate member is in the past year, and the same for questions. I just don't know how to do this. I have a hacker way to do what I want, where I basically UNION the above query with an almost identical copy of myself, except that I use SUM (0) for numparties and numquestions, and the WHERE statement is there. where date <lt; = instead of>. I feel that this is not the best way to do this.

? !

Answer 1

: http://www.w3schools.com/sql/sql_join_left.asp. , .

Answer 2

, :

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

WHERE.

Answer 3

, . - , . "x".

, , , , .

Select distinct users.id,users.name from users

. , , ... .

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

Hope the logic here makes sense to you. Use one query to get a list of users and join this subquery to get their number.

edit to add: this will return zeros at any time when there are no entries. You can make your select show nulls statement equal to 0