CODEWITHSUNDEEP
mysqlleft-join
CHinAU
CHinAU

Reputation: 15

MYSQL query return 0 when no record for date

I have this query:

Select
  X.type1transsum, Y.type2transsum,
  (X.type1transsum + Y.type2transsum),
  X.`date`
From (
  Select sum(trans) as type1transsum, `date`
  from type1trans where shopcode = 1037
  group by `date`
) X 
Left join (
  Select sum(trans) as type2transsum, `date`
  from type2trans where shopcode = 1037
  group by `date`
) Y on X.`date` = Y.`date`

The type2trans table has records for each date, while in type1trans there are no records for some days (as those days have no transactions).

For the dates where there is no records in the type1trans then there is no record shown for the type2 dates and a total for that date being the same as type2 since type1 null. How can I get type1 to return null for the date?

Upvotes: 0

Views: 155

Answers (1)

Ilmari Karonen
Ilmari Karonen

Reputation: 50328

Use a RIGHT JOIN instead of a LEFT JOIN (or just swap the two subselects around in the query).

A useful rule to remember the difference between left and right joins is that a left join includes every row on its left side, even if there's no matching row on the right, while a right join, conversely, includes every row on its right side.

Upvotes: 1

Related Questions