Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: I would like to take the results from 2 rows and combine them.

    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.

  2. #2
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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?

    Code:
    SELECT
    t2.msg_seq
    , t3.lname AS sender
    , t4.lname AS recipient
    , t1.subject
    , t1.message
    , t1.stamp
    FROM
      mail_message AS t1
    Inner
      Join mailbox AS t2
        ON t1.msg_seq = t2.msg_seq
    Inner
      Join employee AS t3
        ON t3.employee_id = t2.employee_id
    Inner
      Join employee AS t4
        ON t4.employee_id = t3.employee_id
          AND t4.lname <> t3.lname  <---------------This is not correct
    GROUP BY
    t2.msg_seq

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    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.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.
    Last edited by Frunkie; 08-18-09 at 05:03.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie
    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.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    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?


  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Is there any chance that we could discuss a new db design?

  10. #10
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie
    The recipient_id is also put into the mailbox table but on a *new row*.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    mmmff

    i just choked on my sammich
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •