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
:cry: still the exact same 10 results. No luck so far.

Re: Query Help

Posted: Mon May 12, 2008 3:13 am
by EverLearning
:banghead:

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 :D