SQL: Using UNION To Create An Inbox Of Messages and Replies
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:
| Msg_ID | User_ID | Sent | Subject | Body |
| 1 | 1 | 2010-12-07 12:34:56 | First test | This is a test |
| 2 | 1 | 2010-12-07 12:36:12 | Second test | This is another test |
There's then a table called "Replies" which looks like this:
| Reply_ID | Msg_ID | User_ID | Sent | Body |
| 1 | 1 | 2 | 2010-12-07 12:35:12 | This is a reply. Seems to work. |
| 2 | 2 | 2 | 2010-12-07 12:38:45 | This is another reply. Got your second message too |
| 3 | 2 | 1 | 2010-12-07 12:40:54 | Cool. Glad I could help. |
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:
| Sent | Subject | From |
| 2010-12-07 12:40:54 | Re: Second test | User One |
| 2010-12-07 12:38:45 | Re: Second test | Admin Two |
| 2010-12-07 12:36:12 | Second test | User One |
| 2010-12-07 12:35:12 | Re: First test | Admin Two |
| 2010-12-07 12:34:56 | First test | User One |
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...
Skip to the 
You can subscribe to an individual
Just a though Jake, could this have been done with just the one table - aren't replies messages too? ( not spent a great deal of time thinking about this so there may be a good answer why not! )
Reply
Undoubtedly, yes. In fact this was my fall back plan in case I didn't work out the above solution. I always knew 1 table would work but it would feel like I'd given up and taken the easy route, which is no way to learn something new.
I guess the question is whether it *should* be one table. I'd hate to think I were using 2 for the sake of it or just plain doing it wrong.
There are a couple of columns I left off the tables which apply to my system and specific to each table. It could be done with one table but would feel messy.
Reply
Show the rest of this thread
I was kinda thinking the same thing. Just have a field called frm_type and you could populate based on a button pushed or by the form or whatever you want.
Reply
If Sent for replies Reply_ID = 1 is equal 2010-12-07 12:41:12 (for example) i.e. later than it is sent message "second test"
SELECT Msg_ID,
NULL as Reply_ID,
Sent,
Subject,
(SELECT Users.FullName as [SentBy] FROM Users WHERE Users.User_ID = Messages.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 Msg_ID DESC, Sent DESC;
Excuse my bad English!
Reply