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!
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!