CODEWITHSUNDEEP
phpmysql
Vladimir
Vladimir

Reputation: 1622

How to combine these 2 queries into 1 and avoid extra code

I have 2 tables in database, table users lists all users and their inviters (referrers) , table donations lists all the donations

The only connection between 2 tables is the user_id in table donations which is the id (Primary AI) in table users

What i want to do is get count of all users in table users which have ref='$referrer' and then see how many of them have donated and they are in donations table (each user can donate multiple times so it shouldn't count 2 donations as 2 users)

What i have tried:

first i get each ref of that inviter

SELECT id FROM users WHERE ref='$referrer'

then i put it in a while() loop and for each one of them i check if they have a donation

SELECT * FROM donations WHERE user_id='$id'

if they are found that counts as 1++

Issue

issue is if a user invited +500 people, then i send 500 queries in a loop with PHP, which takes time and resource

What is needed

I need a single Query with sub-Query to take only referrer id (ref='$referer') and give me count of all user X's referrals (every user with ref='55' in table users for example) which also have at least one donation in donations table

something like:

SELECT COUNT(*) FROM donations WHERE user_id = (SELECT id as user_id FROM users WHERE ref='$referer')

Can any one please help me with that

Upvotes: 1

Views: 57

Answers (2)

Joel Cox
Joel Cox

Reputation: 3469

Try this:

SELECT users.id, COUNT(*) `donations_count`
FROM users
INNER JOIN donations ON user.id = donations.user_id
WHERE users.ref='$referrer'
GROUP BY users.user_id

Upvotes: 1

Manigandan Arjunan
Manigandan Arjunan

Reputation: 2265

SELECT COUNT(*) FROM donations d INNER JOIN users u ON d.user_id=u.id WHERE u.ref='$referrer' GROUP BY d.user_id

Upvotes: 3

Related Questions