CODEWITHSUNDEEP
mysql
karlosuccess
karlosuccess

Reputation: 885

Get rows with condition from 2 tables

I need to get the top 3 referrers along with his number of referrals but only the qualified ones (table_2). I hope it makes sense. So far I have the following query. Please help. Thank you

SELECT count(*) as total_referrals, referrer
FROM table_1
WHERE table_2.qualified = '1'
GROUP BY referrer
ORDER BY total_referrals DESC 
LIMIT 0,3

table_1

referrer   referral
user1      user89
user1      user54
user1      user23
user1      user56
user2      user89
user2      user23
user2      user45
user3      user78
user3      user14
user4      user10
user5      user98
user5      user56
...

table_2

referral   qualified
user89       1
user54       0
user23       0
user56       1
user89       1
user23       1
user45       0
user78       1
user14       1
user10       0
user98       1
user56       1
...

Upvotes: 0

Views: 30

Answers (2)

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

UPDATE 2

http://sqlfiddle.com/#!9/2f598/5

The following query seems to do the job:

SELECT aa.referrer, COUNT(*) AS total_referrals
FROM table_1 AS aa
INNER JOIN (
    SELECT referral
    FROM table_2
    WHERE qualified = 1
    GROUP BY referral
) AS _bb
ON aa.referral = _bb.referral
GROUP BY aa.referrer
ORDER BY total_referrals DESC
LIMIT 3;

Upvotes: 0

Shadow
Shadow

Reputation: 34232

You need to reference both tables in the from clause and decide whether your field name is referrer or username:

SELECT count(table_1.referrals) as total_referrals, referrer
FROM table_1 left join table_2 on table_1.referrals=table_2.referrals
WHERE table_2.qualified = '1'
GROUP BY table_1.referrer
ORDER BY total_referrals DESC 
LIMIT 0,3

Upvotes: 1

Related Questions