Query Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post 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;
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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 :)
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post 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?
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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'
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post 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?
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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'
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post by shiznatix »

:cry: still the exact same 10 results. No luck so far.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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'
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post 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'
Attachments
Screenshot.png
Screenshot.png (34.61 KiB) Viewed 816 times
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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'
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post by EverLearning »

Any progress?
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post 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.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post by EverLearning »

What can I say, I like challenges :D
Post Reply