Database query with joins - help please!

Dr Pepper

Hi,

I'm using mysql. I have 2 tables, abox and atrans.
- abox is a list of boxes
- atrans shows a list of transactions for boxes (each entry in atrans has an id, transaction amount, transdate and a boxid - which links it to the abox table)

So, this query is supposed to get the sum of all transactions for each box within a specific date range, and it does! However, I also want it to return boxes in the list which have NO transactions within the specified date range (in which case sum(t.amount) should equal zero or null).

select b.id, sum(t.amount)
from abox b left join atrans t on b.id=t.boxid
where t.transdate between '2008-07-03 06:00:00' and '2008-07-10 06:00:00'
group by b.id
order by b.id;

return:
+----+---------------+
| id | sum(t.amount) |
+----+---------------+
|  1 |          2035 |
|  3 |          1185 |
|  4 |            85 |
|  5 |           640 |
...

As you can see, the returned table does not include the box with id=2 because there were no transactions for box 2 in the given date range.

BTW - No this is not college homework. I have been programming for years and I have a fairly good grasp of SQL but I just can't get my head around this one! I think it's something got to do with using sum/group by and left join together ??

Thanks,
Brian

Skittle

Did you consider using a UNION statement back onto the atrans table where the count of the grouped rows = zero?

leeroybrown

Remember that your WHERE clauses are applied *after* the JOIN. The LEFT JOIN is including all the boxes but all these rows are excluded by the date range.

If your MySQL version supports subqueries then I'd suggest pre-filtering your transactions by date in a subquery then using a LEFT JOIN with the boxes.

Dr Pepper

Thanks for the replies! I had a go at leeroybrown's solution. Wasn't sure exactly what you were talking about but gave it a go and it works perfectly! I didn't know you could use results from a subquery in a FROM clause like that.

In case anyone has a similar problem, here is the query which worked:

select b.id, sum(t.amount)
from abox b left join (select * from atrans where transdate between '2008-07-03 06:00:00' and '2008-07-10 06:00:00') t on b.id=t.boxid
group by b.id
order by b.id;

Thanks again!