query shows values once

[thompom]

Hi,
the following query counts the number of response
and groups by this figure.
it then groups this figure depending on the amount between 1 and 15 (persistancestage.stage is a table with values from 1 to 15)

the result should be totals for each user of this figure.

the following query almost works but only shows 1 stage per user. I would like all stages for each user.

existing results
user stage responsecount
USERA 1 2
USERB 3 6
USERC 2 3

needed results
user stage responsecount
USERA 1 2
USERA 2 1
USERA 3 5
USERB 1 6
USERB 2 2
USERC 5 3

SELECT users.user, persistancestage.stage, persistancestage.responsecount
FROM users 
	LEFT JOIN (
		SELECT stage, count(responsecount) AS responsecount, responsecount.userid 
		FROM persistancestage 
			LEFT JOIN ( 
				SELECT count(response.responseid) AS responsecount, event.userid 
				FROM event
				INNER JOIN action ON action.eventid = event.eventid 
				INNER JOIN response ON response.actionid = action.actionid 
				WHERE response.reasonid <> 'First Contact'
				AND date(action.actiondate) BETWEEN '20100801' AND '20100831'
				GROUP BY event.eventid
			) AS responsecount ON responsecount.responsecount = persistancestage.stage 
		GROUP BY persistancestage.stage
	) AS persistancestage ON persistancestage.userid = users.userid

please let me know if not being clear
and thanks in advance

 

[thompom]

in case anyone else need similar results - dumped the stage table and ended up with

SELECT users.userid, users.userf, users.userl, deptbut.deptbut, COUNT(persistancestage.responsecount) AS response, persistancestage.responsecount AS stage
FROM users 
       LEFT JOIN ( 
                SELECT count(response.responseid) AS responsecount, event.userid 
                FROM event
                INNER JOIN action ON action.eventid = event.eventid 
                INNER JOIN response ON response.actionid = action.actionid 
                WHERE response.reasonid <> 'First Contact'
                AND date(action.actiondate) BETWEEN '20100801' AND '20100831'
                GROUP BY response.actionid
ORDER BY response.actionid
    ) AS persistancestage ON persistancestage.userid = users.userid

was easy when I thought about it ;-)