Gaming need mysql help

[MFDC12]

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]

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]

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!