Right Self-joining

[Tyler Hains]

Hello,

This query seems to me like it should return all group-store combinations with a count of transactions for the year 't1', and a count for the month selected by 't'.

SELECT t1.group_id, t1.storecode, count(t.trans_id) as numtrans, count(t1.trans_id) as yrtrans
from transactions t right join transactions t1 on (t1.trans_id = t.trans_id)
where t.proc_dtime > '2005-05-01' and t.proc_dtime < '2005-05-31'
and t1.proc_dtime > '2005-01-01'
group by t1.group_id, t1.storecode
order by yrtrans

What it is returning looks like it is performing an inner join - the count for t and t1 are the same, and group-store combinations with transactions outside of the t date range are excluded.

Does anyone know why the right join is not being performed?

Thanks,
Tyler



Edited 1 time(s). Last edit at 06/08/2005 11:29AM by Tyler Hains.