I have a normalized table that is causing me issues because I need to have a *combined* result set and what is happening is that I am getting two rows instead of one.

I am developing an email type system. This system will send and receive messages to users from my employee table. If a person sends an email, he will be the sender and whoever he sends to will be the recipient. Conversely, if the recipient sends an email back to the original sender, the roles now become reversed.

In my application, I am trying to show *in a single query* who is who and I can't seem to do it. For example, I am getting two rows; one for the sender and the other for the recipient which is correct BUT, I need the sender and recipient on the same damn row NOT different rows.

If needed, I can post the SQL.

Can someone please help?

Thanks.

I should mention that this employer doesn't want DDL posted on a public forum. I'm sorry, I know it makes it difficult to help me if not impossible but they have my hands tied. If nobody can help, I totally understand. I'm free to post SQL but not the table structures. (how stupid)

Thanks

I'm almost there with this I think. I have a self join operation which I;m almost certain that I need to do this but I don't have the constraints correct. Can someone please help?

I think that your mail_message table should have a sender_id and a recipient_id field. If that is the case, your statement should look like this:

SELECT t1. stamp, t1.message, t1.subject, t2.lname AS Sender, t3.lname AS Recipient
FROM mail_message AS t1
INNER JOIN employee AS t2 ON t1.sender_id = t2.employee_id
INNER JOIN employee AS t3 ON t1.recipient_id = t3.employee_id

This is a simplified version, as I can see you have an additional mailbox table.

Hi Aflorun,

I already thought of doing that but it won't work because there are different flags in the table. Some flags are for the sender and others are for the recipient. If I merge them together then I won't be able to tell which flags go to which person. Hope that makes sense.

EDIT:

You know, maybe it would work. I can't think clearly, been at this WAAAAAAAAAAAAAAAAY too many hours. I'll think about it though. Thanks for the idea.

sorry, no, it doesn't

if you do not have sender_id and receipient_id, how do you know who sent the message?

and maybe you can tell us what the flags are for?

also, using a GROUP BY is wrong, because you're not aggregating anything

Thanks for responding Rudy.

I have the sender and recipient ids.. the application code is inserting these values when a new message is created or responded to. All I am doing here is showing the user who the message is from before they open it. That's all. That's the entire purpose of this query.

I have a type field in the mailbox table (box_type) that specifies if the message should belong to the in or outbox. So, for every sender_id *row* there will also a corresponding recipient_id *row*. When I query for employee_id = '1' for example, I should get the BOTH rows. I need to take both of these rows and combine or concat whatever you want to call it but I need both of these columns on the same row.

The flags are used to determine if the message is a Cc and also whether it has been flagged for deletion. These flags don't have anything to do with this query though. If I were to take aflorin27's advice and make another FK into the mailbox table, it will require that I rewrite a lot of my app code and I don't want to do that, besides it not being a correct design.

Is that a little clearer?

nope, sorry, it's worse

you may be stuck if you can't tell people what the tables look like

earlier, you said you had a query that worked and that was returning two rows

my advice is, use that query, and combine the two rows into one row using your application language (php or whatever)

Is there any chance that we could discuss a new db design?

I'm going to post how this is currently working and see if we can go from there.

The employee logs into the system and with their employee number. This employee number is used to identify them as the sender of the message. The user types the message and submits the form. Once the form is submitted, I take the employee_id and put it into the mailbox table, then I take the message itself and store it in the mail_message table.

Now, here's the other side; the recipient.

The sender was given the choice of who to send the message to in the same form I just mentioned. Now I have the recipient_id. The recipient_id is also put into the mailbox table but on a *new row*.

The difference between these two rows is that one is an inbox and the other is an outbox, delineated by the "box_type" type field.

I hope this is a bit clearer.

mmmff

i just choked on my sammich

here's how you do it -- one table for the messages, and a separate table for the recipients

the message may contain the userid of the sender, assuming that you cannot have multiple senders for a single message

the recipient table has one row per recipient, assuming that a message may be sent to more than one person, you know, like in most email and messaging systems i've ever seen

the recipients table does not carry the actual text of the message, just a flag as to whether the recipient has seen it or not

do not call the messages table a "mailbox" table

mailboxes are something else

lol.. I figured it was bad but I didn't realize it was that bad.

Anyway, here's what happened today. I got up, got your suggestion and modeled it exactly like you said. You're absolutly correct, tis a better design and more flexable. I dumped some sample data into the database and tested here at home before going into work. It worked perfectly; much, much better than the design I had.

As always, thanks for all your help Rudy.