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