Gaming need mysql help

MFDC12

Well-Known Member
OP
Member
Joined
Oct 12, 2007
Messages
790
Trophies
0
Age
30
Website
Visit site
XP
516
Country
United States
I need some help with a mysql query but I can't figure it out for the life of me.
I need to count the amount of people who were referred to the site by another member, as long as they have one post. This would not be a problem for me if they were not in two different tables and having needed a count (which I am having trouble with).

Here are the two queries I want to join:
SELECT DISTINCT u.ref_by, u.id FROM users AS u WHERE u.status = 'confirmed' GROUP BY u.ref_by ORDER BY refs DESC;
SELECT COUNT(*) FROM history WHERE userid = u.id AND posts > 1";

Problems:
I dont know how to count the total amount.
The part in bold, I am not sure how to do that. I have tried unions and all types of joins, but I can't seem to get this to be used from the first query (always results in something life "u.id unknown in where clause")

Any help is appreciated, this has given me a headache for about 5 hours now and I give up.
Thanks!
 

Jiggah

Well-Known Member
Member
Joined
Nov 9, 2002
Messages
1,223
Trophies
0
Website
Visit site
XP
279
Country
United States
Why wouldn't you use the first select statement to find the people with one post?


For example,

SELECT DISTINCT u.ref_by, u.id FROM users as u, history WHERE u.id = userid and u.status = "confirmed" and post >= 1;

You can then use that select statement within a count:

SELECT COUNT(*) FROM (SELECT DISTINCT u.ref_by, u.id FROM users as u, history WHERE u.id = userid and u.status = "confirmed" and post >= 1) as CONUSER;


The reason why your second query is messing up is because for that SELECT statement you're only referencing the "history" table. It doesn't know what u.id is.
 

MFDC12

Well-Known Member
OP
Member
Joined
Oct 12, 2007
Messages
790
Trophies
0
Age
30
Website
Visit site
XP
516
Country
United States
Jiggah said:
Why wouldn't you use the first select statement to find the people with one post?


For example,

SELECT DISTINCT u.ref_by, u.id FROM users as u, history WHERE u.id = userid and u.status = "confirmed" and post >= 1;

You can then use that select statement within a count:

SELECT COUNT(*) FROM (SELECT DISTINCT u.ref_by, u.id FROM users as u, history WHERE u.id = userid and u.status = "confirmed" and post >= 1) as CONUSER;


The reason why your second query is messing up is because for that SELECT statement you're only referencing the "history" table. It doesn't know what u.id is.

You got exactly what I'm trying to do except for one thing - i need it so it groups it by ref_by, rather than a total number.

Thanks for helping so far!
 
General chit-chat
Help Users
    Veho @ Veho: https://i.imgur.com/S2L9j5A.mp4