Tuesday 6 September 2011

SQL Self-Joins

I was trying to simplify our troubleshooting efforts when dealing with a certain type of issue in one of our systems, by using a sql query, and properly apportion blame, i.e. tell the customer it’s their fault, when I hit an issue. My query was misbehaving, it was not working properly, returning twice some values, but others were ok.

After a bit of thinking, I realized that what I needed was a way of using two join conditions. I wasn’t sure whether this was possible, but it turns out that it is indeed possible and very simple.

All that is needed is this, in bold, full query for reference:
Select t1.id, t1.session, t1.date from
(select id, session, date from mytable where ….)t1
Join
(select count(*) as items, idname, scheduledsession from myothertable
Group by idname, scheduledsession)t2
on (t1.id = t2.id and t1.session = t2.session)
order by t1.id desc

No comments:

Post a Comment