I'm working on a system that has an internal "messaging system". Users who are logged-in can contact the "admin team" with any queries about the system and the administrators can reply to these messages, as, in turn, can the original user.

There are two tables involved. The first is the main "Messages" table, which looks like this:

There's then a table called "Replies" which looks like this:

As you can see. The Replies table is tied back to the Message table via the Msg_ID foreign key. All very straightforward. Notice, however, that the replies don't have their own subject, but the Inbox we're creating will need one.

The problem, for me anyway, was working out how to display an Inbox to the Administrators.

The Inbox for the normal users was easy. They only ever see replies (this isn't the "Sent" folder after all) and so a simple SQL SELECT on the Replies table covered that.

Administrators need to see both the main messages and the replies too. They should be displayed in a time-ordered way, like this:

Knowing no better I tackled this by trying to JOIN the two table together using the Msg_ID foreign key. The trouble with that (as will be very obvious to most of you) is that the matching rows get joined in to one row. So, any Message that has a Reply will only appear in the inbox once - as the Reply. The message itself won't appear - which isn't how you'd expect an inbox to behave.

After much head scratching and fruitless Googling I found the solution. It has to use a UNION of two SELECT statements. Seems so obvious now. It's just been so long since I covered the very basics of SQL that I'd forgotten there was anything other than JOINs.

So, here's what the SQL looks like:

SELECT Msg_ID, NULL as Reply_ID, Sent, Subject, (SELECT Users.FullName as [SentBy] FROM Users WHERE Users.User_ID = Message.User_ID)
FROM Messages UNION SELECT Msg_ID, Reply_ID, Sent, 'Re: '+(SELECT Subject FROM Messages WHERE Msg_ID = Replies.Msg_ID) as Subject, (SELECT Users.FullName as [SentBy] FROM Users WHERE Users.User_ID = Replies.User_ID)
FROM Replies ORDER BY Sent DESC;

My quest to master SQL continues. Perhaps I need to take a step back and master the very basics first...

IF2 was supposed to fix a bunch of Domino annoyances, in particular out-of-memory HTTP crashes. Always good to fix those.

As it turns out, Vlad installed IF2 and instead of getting better performance, he started getting all sorts of new problems. These included MIME content moved to attachments, HTML code in message body, and Blackberry messages not showing message body.

Oops. Once he removed IF2, everything went back to normal.

So, here's where it gets weird. A day or so later, IF2 was removed from the support portal. Then, later IF2 was put back on the support portal, but with a whole new set of hotfix numbers.

Frankly, it would have made everyone's life easier if the newly replaced update had been called IF2a or IF3, but that's sadly not the way it worked out.

In any case, if you're using IF2, be sure it's the new IF2.

Good luck and you all be safe out there, okay?

Joe is a ZATZ associate editor. Prior to this job, he was customer support manager for the FileFlex database engine.