Mysql left join not returning null values

Question

i have a table called users which has users and respective referralIds. Some of the users do not have referralIds and are NULL.

I have a second table called transactions where sum(transactions.amount) will give me the revenue that the customer generated as long as transaction_type = 1

Problem: I am doing a left join that will show me revenue generated per referralId. I want a group by that shows me revenue generated by users that do not have referralIds a well. However, Null referralId is returning NULL revenue as well

SELECT
 users.referralId, b.revenue 
 FROM users
        LEFT JOIN
    (SELECT 
         users.referralId, SUM(transactions.amount) AS revenue
    FROM
        transactions
    LEFT JOIN users ON users.username = transactions.username
    WHERE
        transaction_type = 1 and date(created_at) between @start_date and @end_date
    GROUP BY users.referralId) b ON users.referralId = b.referralId
group by referralId

Rahul · Accepted Answer · 2016-12-13 11:11:38Z

1

In that case consider doing a INNER JOIN instead in your outer query. Moreover, don't see a reason why you need that outer query at all. Your inner subquery should be able to get the required data.

SELECT
 users.referralId, b.revenue 
 FROM users
        INNER JOIN
    (SELECT 
         users.referralId, SUM(transactions.amount) AS revenue
    FROM
        transactions
    LEFT JOIN users ON users.username = transactions.username
    WHERE
        transaction_type = 1 and date(created_at) between @start_date and @end_date
    GROUP BY users.referralId) b ON users.referralId = b.referralId
group by referralId

answered Dec 13, 2016 at 11:11

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bini teshome · Accepted Answer · 2016-12-13 11:12:51Z

0

LEFT OUTER JOIN worked for me. you should give it a try

answered Dec 13, 2016 at 11:12

bini teshome

11 silver badge2 bronze badges

Collectives™ on Stack Overflow

Mysql left join not returning null values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related