MySQL Forums
Forum List  »  InnoDB

Right Self-joining
Posted by: Tyler Hains
Date: June 08, 2005 10:15AM


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?


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

Options: ReplyQuote

Written By
Right Self-joining
June 08, 2005 10:15AM
June 08, 2005 07:35PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.