Joining two columns to same table - heads-cratcher

• Roman Vinnik

  SSC Veteran

  Points: 221

  More actions

  August 17, 2015 at 5:04 am

  #302026

  Hi,

  Im getting myself all tied up in knots with this query and am hoping someone can assist me.

  I have two tables: "Users" and "Messages".

  The Messages table has a "UserFromID" and an "UserToID", indicating a message was sent from a user to a user.

  The Users table has a UserID, FirstName, LastName and UserGroup.

  Im trying to get all messages sent by or to a user in a specific group, with the First and Last names of the sending and receiving users.

  Here is my tables and data:

  CREATE TABLE tmp_users (userID INT, GroupID INT, UserName VARCHAR(10), FullName VARCHAR(100))

  CREATE TABLE tmp_Messages (aKey INT IDENTITY, FromUserID INT, ToUserID INT, msg VARCHAR(100))

  INSERT INTO tmp_users VALUES (1,1,'John','John Smith')

  INSERT INTO tmp_users VALUES (2,1,'Jane','Jane Doh')

  INSERT INTO tmp_users VALUES (3,1,'Bob','Bob Newheart')

  INSERT INTO tmp_users VALUES (4,2,'Tom','Tom Jones')

  INSERT INTO tmp_users VALUES (5,2,'Frank','Frank Frakie')

  INSERT INTO tmp_users VALUES (6,3,'Joe','Joe GI')

  INSERT INTO dbo.tmp_Messages (FromUserID, ToUserID, msg) VALUES (1, 2, '1-2')

  INSERT INTO dbo.tmp_Messages (FromUserID, ToUserID, msg) VALUES (1, 3, '1-3')

  INSERT INTO dbo.tmp_Messages (FromUserID, ToUserID, msg) VALUES (1, 4, '1-4')

  INSERT INTO dbo.tmp_Messages (FromUserID, ToUserID, msg) VALUES (1, 5, '1-5')

  INSERT INTO dbo.tmp_Messages (FromUserID, ToUserID, msg) VALUES (1, 6, '1-6')

  INSERT INTO dbo.tmp_Messages (FromUserID, ToUserID, msg) VALUES (2, 1, '2-1')

  INSERT INTO dbo.tmp_Messages (FromUserID, ToUserID, msg) VALUES (2, 3, '2-3')

  INSERT INTO dbo.tmp_Messages (FromUserID, ToUserID, msg) VALUES (2, 4, '2-4')

  INSERT INTO dbo.tmp_Messages (FromUserID, ToUserID, msg) VALUES (5, 6, '5-6')

  userID GroupID UserName FullName

  ------ ------- -------- ------------

  1 1 John John Smith

  2 1 Jane Jane Doh

  3 1 Bob Bob Newheart

  4 2 Tom Tom Jones

  5 2 Frank Frank Frakie

  6 3 Joe Joe GI

  aKey FromUserID ToUserID msg

  ---- ---------- -------- ----

  1 1 2 1-2

  2 1 3 1-3

  3 1 4 1-4

  4 1 5 1-5

  5 1 6 1-6

  6 2 1 2-1

  7 2 3 2-3

  8 2 4 2-4

  9 5 6 5-6

  The query Im running so far is wrong, but here it is...

  SELECT t.FromUserID, t.ToUserID, t.msg, u.UserName AS UserFrom,

  u.GroupID AS FromGroup, u2.UserName AS UserTo, u2.GroupID AS ToGroup

  FROM tmp_Messages t

  LEFT JOIN (SELECT UserID, GroupID, UserName FROM tmp_users WHERE GroupID = 3) u

  ON u.UserID = t.FromUserID

  LEFT JOIN (SELECT UserID, GroupID, UserName FROM tmp_users WHERE GroupID = 3) u2

  ON u2.UserID = t.ToUserID

  WHERE coalesce(u.userID, u2.UserID, -1) <> -1

  which brings back

  FromUserID ToUserID msg UserFrom FromGroup UserTo ToGroup

  ---------- -------- ---- -------- --------- ------ -------

  1 6 1-6 NULL NULL Joe 3

  5 6 5-6 NULL NULL Joe 3

  As you can see, im missing the details of one of the users.

  I know what the problem is, I just cant figure out how to get this working without using temp tables, which I cant do in the production version.

  Any help will be greatly appreciated

  Thanks

  Roman

• SQLCJ

  SSCommitted

  Points: 1791

  More actions

  August 17, 2015 at 5:40 am

  #1820388

  very dumb question probably but what user details are you missing exactly ? Group 3 has only one user isn't it ?

• JohnG69

  Hall of Fame

  Points: 3114

  More actions

  August 17, 2015 at 6:27 am

  #1820410

  If I understand your question properly,

  Here's what I came up with...

  SELECT t.FromUserID, t.ToUserID, t.msg, FromU.UserName AS UserFrom,

  FromU.GroupID AS FromGroup, ToU.UserName AS UserTo, ToU.GroupID AS ToGroup

  FROM tmp_Messages t

  LEFT JOIN tmp_users FromU ON FromU.UserID = t.FromUserID

  LEFT JOIN tmp_users ToU ON ToU.UserID = t.ToUserID

  WHERE ToU.GroupID = 3 or FromU.GroupID = 3

  Let me know if it is what you were looking for.

  thanks

  JG

• Roman Vinnik

  SSC Veteran

  Points: 221

  More actions

  August 19, 2015 at 4:00 am

  #1820972

  Hi,

  Its working perfectly

  I must have had a brain freeze! I tried exactly that earlier on but was getting duplicate results (in other words I was doing something stupid and not realising it).

  Thanks a lot for your assistance!

  Regards

  Roman