The query is still giving me the same result of 10. The magic number this query has to return is 7, did the calculation manually. The query as it stands now is:
SELECT COUNT( id ) AS signups
FROM (
SELECT DISTINCT (
um.fk_vb_user_id
) AS id
FROM rbf_user AS us
LEFT JOIN rb_usermap AS um ON um.fk_vb_user_id = us.userid
WHERE um.added_time
BETWEEN 1204322401
AND 1204408799
AND um.fk_room_id = '3'
GROUP BY um.fk_vb_user_id
HAVING COUNT( um.fk_vb_user_id ) =1
) AS unique_values
LIMIT 0 , 30;
SELECT DISTINCT
um.fk_vb_user_id
FROM rbf_user AS us
LEFT JOIN rb_usermap AS um ON um.fk_vb_user_id = us.userid
WHERE um.added_time
BETWEEN 1204322401
AND 1204408799
AND um.fk_room_id = '3'
GROUP BY um.fk_vb_user_id
HAVING COUNT( um.fk_vb_user_id ) =1
Maybe we'll spot something that isn't supposed to be there
that returns the 10 fk_vb_user_id's that have a record with fk_room_id of 3 and added_time between those times. The problem is that there are 3 of them that have multiple rows in there. One of them has 6 rows with that fk_vb_user_id and the other 2 have 2 rows with the same fk_vb_user_id.
What it is boiling down to is this HAVING COUNT( um.fk_vb_user_id ) =1 is just not working as it should.
Epiphany: Ok lets say fk_vb_user_id 987 is being returned in this query but shouldn't be. This 987 guy will have 3 rows in the table, each with a different fk_room_id. So lets say these 3 rows look like:
fk_vb_user_id, fk_room_id
987, 3
987, 23
987, 12
I don't want this guy to be returned because, even though he has a 3 as an fk_room_id, he also has other rows. I only want people returned that ONLY have 1 row and that row has an fk_room_id of 3. Does that help?
shiznatix wrote:HAVING COUNT( um.fk_vb_user_id ) =1 is just not working as it should.
It is working, but the condition um.fk_room_id = '3' in WHERE clause disregards the rows with different fk_room_id. Thats why you're getting those users
shiznatix wrote:I only want people returned that ONLY have 1 row and that row has an fk_room_id of 3.
To do so we would need to move the um.fk_room_id = '3' condition to HAVING clause, so the final query should be
SELECT DISTINCT um.fk_vb_user_id
FROM rbf_user AS us
LEFT JOIN rb_usermap AS um ON um.fk_vb_user_id = us.userid
WHERE um.added_time
BETWEEN 1204322401
AND 1204408799
GROUP BY um.fk_vb_user_id
HAVING COUNT( um.fk_vb_user_id ) = 1 AND um.fk_room_id = '3'
MySql Manual wrote:MySQL 5.0.2 and up allows HAVING to refer to columns in the SELECT list, columns in the GROUP BY clause, columns in outer subqueries, and to aggregate functions.
So put um.fk_room_id in the SELECT list and lets see what happens
SELECT DISTINCT um.fk_vb_user_id, um.fk_room_id
FROM rbf_user AS us
LEFT JOIN rb_usermap AS um ON um.fk_vb_user_id = us.userid
WHERE um.added_time
BETWEEN 1204322401
AND 1204408799
GROUP BY um.fk_vb_user_id
HAVING COUNT( um.fk_vb_user_id ) = 1 AND um.fk_room_id = '3'
SELECT DISTINCT um.*
FROM rbf_user AS us
LEFT JOIN rb_usermap AS um ON um.fk_vb_user_id = us.userid
WHERE um.added_time
BETWEEN 1204322401
AND 1204408799
GROUP BY um.fk_vb_user_id
HAVING COUNT( um.fk_vb_user_id ) = 1 AND um.fk_room_id = '3'
SELECT DISTINCT us.*
FROM rbf_user AS us
LEFT JOIN rb_usermap AS um ON um.fk_vb_user_id = us.userid
WHERE um.added_time
BETWEEN 1204322401
AND 1204408799
GROUP BY um.fk_vb_user_id
HAVING COUNT( um.fk_vb_user_id ) = 1 AND um.fk_room_id = '3'
The first one returns all unique user ids but some of those ids exist with other fk_room_ids in the table so they shoudn't be returned in the first place. The added_timeThe first one returns all unique user ids but some of those ids exist with other fk_room_ids in the table so they shouldn't be returned in the first place.
The second query returns this error: #1054 - Unknown column 'um.fk_room_id' in 'having clause'
Nothing new comes to my mind. Only thing I can suggest is that you provide some sample data I can tinker with(a 10-20 rows from your rb_usermap table with different room_id-s, and corresponding rows from rbf_user table), so I can replicate the behavior, and maybe fix this thing.
SELECT um.*
FROM rb_usermap um
LEFT JOIN rbf_user AS us ON um.fk_vb_user_id = us.userid
GROUP BY um.fk_vb_user_id
HAVING COUNT(fk_vb_user_id) = 1
AND um.added_time BETWEEN 1204322401 AND 1204408799
AND fk_room_id = '3'
whoops sorry I thought I had already posted a long rambling thank-you but apparently I was just drunk. So here it is...
THANK YOU! If there was a kissing smiley face I you post like 10 of them but since there isn't I will just leave you with this explanation of what I would have done if they were there.
But seriously, thank you that was it. Thanks for sticking around and helping me get that query up and running just peachy. Huge thanks.