SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Message table needs to obtain the name of sender and receiver from members table

    What is the best way to modify my query so that I also get the full name of the receiver in addition to the sender?

    Thanks!

    select u.uID
    , concat (u.firstname, ' ', u.lastName) as sender
    , m.senderID
    , m.receiverID
    , m.senderMessage
    from users u
    inner join messages m
    on u.uID = m.senderID
    where u.uID = m.receiverID or u.uID = m.senderID;
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Join the user table again (with another alias), this time on the receiverID

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Join the user table again (with another alias), this time on the receiverID
    and removes that WHERE Clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That didn't work, unless I misunderstood what you were saying:

    select u.uID
    , concat (u.firstname, ' ', u.lastName) as sender
    , concat (x.firstname, ' ', x.lastName) as receiver
    , m.senderID
    , m.senderMessage
    , x.receiverID
    from users u
    inner join messages m
    on u.uID = m.senderID
    inner join users x
    on u.uID = x.receiverID;
    Convert your dollars into silver coins. www.convert2silver.com

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    from users u
    inner join messages m
    on u.uID = m.senderID
    inner join users x
    on u.uID = x.receiverID;
    change the part in red to --

    x.uID = m.receiverID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok I tried what you said Rudy and got this error, "Unknown column 'x.receiverID' in field list".

    Thoughts?
    Convert your dollars into silver coins. www.convert2silver.com

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by busboy View Post
    Ok I tried what you said Rudy and got this error, "Unknown column 'x.receiverID' in field list".

    Thoughts?
    Yes. You didn't try what Rudy said
    The part in red is what you wrote, and is wrong. You must change that to the code he posted at the end. And there is no x.receiverID in that code.

  8. #8
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Checking further it was because I had x.receiverID earlier in the select statement before the FROM. I took that out and it works. Thanks to both of you!
    Convert your dollars into silver coins. www.convert2silver.com

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by busboy View Post
    I took that out and it works.
    Very good


Bookmarks

Posting Permissions

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