Page 2 of 2
Re: Query Help
Posted: Fri May 09, 2008 2:36 am
by shiznatix
*sigh*
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:
Code: Select all
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;
Re: Query Help
Posted: Fri May 09, 2008 3:32 am
by EverLearning
Lets forget the
SELECT COUNT( id ) AS signups and lets focus on the part that retrieves the rows that are counted.
What does this query return?
Code: Select all
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

Re: Query Help
Posted: Fri May 09, 2008 7:07 am
by shiznatix
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?
Re: Query Help
Posted: Fri May 09, 2008 9:04 am
by EverLearning
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
Code: Select all
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'
Re: Query Help
Posted: Fri May 09, 2008 9:12 am
by shiznatix
Ok that makes sense but what doesn't make sense is the error I am getting:
#1054 - Unknown column 'um.fk_room_id' in 'having clause'
But that column DOES exist. Whats up with that?
Re: Query Help
Posted: Fri May 09, 2008 9:19 am
by EverLearning
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
Code: Select all
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'
Re: Query Help
Posted: Mon May 12, 2008 3:00 am
by shiznatix

still the exact same 10 results. No luck so far.
Re: Query Help
Posted: Mon May 12, 2008 3:13 am
by EverLearning
OK. Try this two queries, and post their results. It should give us something to work on, i.e. why its returning multiple rows for one id
Code: Select all
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'
and this
Code: Select all
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'
Re: Query Help
Posted: Mon May 12, 2008 3:43 am
by shiznatix
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'
Re: Query Help
Posted: Tue May 13, 2008 5:26 am
by EverLearning
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.
Re: Query Help
Posted: Tue May 13, 2008 8:55 am
by EverLearning
If I interpreted the results I got correctly, this is what we've been looking for:
Code: Select all
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'
Re: Query Help
Posted: Thu May 15, 2008 4:16 am
by EverLearning
Any progress?
Re: Query Help
Posted: Mon May 19, 2008 6:18 am
by shiznatix
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.
Re: Query Help
Posted: Mon May 19, 2008 12:06 pm
by EverLearning
What can I say, I like challenges
