How can I adjust a JOIN clause so that rows that have columns with NULL values are returned in the result?

Question

How can I adjust this JOIN clause so that rows with a NULL value for the CountLocId or CountNatId columns are returned in the result?

In other words, if there is no match in the local_ads table, I still want the user's result from the nat_ads table to be returned -- and vice-versa.

SELECT u.franchise, CountLocId, TotalPrice, CountNatId, TotalNMoney, (
TotalPrice + TotalNMoney
)TotalRev
FROM users u
LEFT JOIN local_rev lr ON u.user_id = lr.user_id
LEFT JOIN (

SELECT lrr_id, COUNT( lad_id ) CountLocId, SUM( price ) TotalPrice
FROM local_ads
GROUP BY lrr_id
)la ON lr.lrr_id = la.lrr_id
LEFT JOIN nat_rev nr ON u.user_id = nr.user_id
INNER JOIN (

SELECT nrr_id, COUNT( nad_id ) CountNatId, SUM( tmoney ) TotalNMoney
FROM nat_ads
WHERE MONTH =  'April'
GROUP BY nrr_id
)na ON nr.nrr_id = na.nrr_id
WHERE lr.month =  'April'
AND franchise !=  'Corporate'
ORDER BY franchise

Thanks in advance for your help!

Apparently MySQL doesn't have full joins http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — Michael J Swart, May 08 '13 at 17:43

score 1 · Answer 1 · answered May 08 '13 at 17:44

1

try the following in where clause while making a left join. This will take all rows from right table with matched condition

eg.

LEFT JOIN local_rev lr ON (u.user_id = lr.user_id) or (u.user_id IS NULL)

answered May 08 '13 at 17:44

Ankush Jain

1,532
1
15
24

I'm sorry, I tried inserting this a few dozen ways with no luck. Not sure if I'm doing it wrong, or if the solution doesn't work. It produces the same result I was getting before. – John May 08 '13 at 18:56

Saic Siquot · Answer 2 · 2013-05-08T19:28:22.247

Use this template, as it ensures that :

you have only one record per user_id (notice all subquerys have a GROUP BY user_id) so for one record on user table you have one (or none) record on subquery
independent joins (and calculated data) are not messed togeder

-

SELECT u.franchise, one.CountLocId, one.TotalPrice, two.CountNatId, two.TotalNMoney, (COALESCE(one.TotalPrice,0) + COALESCE(two.TotalNMoney,0)) TotalRev
FROM users u

LEFT JOIN (
    SELECT x.user_id, sum(xORy.whatever) as TotalPrice, count(xORy.whatever) as CountLocId
    FROM   x                             -- where   x is  local_rev or local_ads   I dont know
    LEFT JOIN  y  on x.... = y....       -- where   y is  local_rev or local_ads   I dont know
    GROUP BY  x.user_id
) as one   on u.user_id  = one.user_id

LEFT JOIN (
    SELECT x.user_id, sum(xORy.whatever) as TotalNMoney, count(xORy.whatever) as CountNatId
    FROM   x                             -- where   x is  nat_rev or nat_ads   I dont know
    LEFT JOIN  y  on x.... = y....       -- where   y is  nat_rev or nat_ads   I dont know
    GROUP BY  x.user_id
) as two   on u.user_id  = two.user_id

How can I adjust a JOIN clause so that rows that have columns with NULL values are returned in the result?

2 Answers2