Lots to say, but I'll be spreading it out.

I'm going to try to force myself to write at least a little blurb daily, but we'll see just how long that lasts.

Anyway, today's odd bit of SQL -- I needed to get a list of all items in a table that had no corresponding child records via a self join. The resulting blurb looks odd, but does in fact work:

SELECT
   Uniques.*, DATEDIFF(DAY, Uniques.StartDate, Uniques.EndDate)
FROM
   #people Uniques LEFT OUTER JOIN #people Related
   ON (Uniques.PersonNumber = Related.PersonNumber AND Related.PersonRecordNumber IS NOT NULL)
WHERE
   Uniques.PersonRecordNumber IS NULL AND
   Related.PersonRecordNumber IS NULL

Works fine, but looks funny. After some optimization, the seemingly conflicting clauses were removed, and placed in the requisite UPDATE statement, but I got a chuckle out of this. (Record names and tables changed to protect the innocent :) )

2 Comments

  • Welcome back again! You know I'm always eager to read your stuff so it's good to see you blogging, especially if you're going daily.



    As for this snippet, with the datediff in there this looks a lot like TSQL. I'm curious what the # signs mean... I'm assuming you're making two different references to the people table, calling one "uniques" and one"related" so you can compare the two.



    Well, you don't touch that datediff result again, so I won't pretent to understand this bit of voodoo! What a crazy one. Glad it worked, but yikes!



    -Eric

  • I expect the # indicate the underlying table is a temporary table.



    Jason, yep, it is amazing what you can do w/T-SQL. But, working with Jim H., you should already know that<g>.

Comments have been disabled for this content.