August 17, 2015 at 5:04 am
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
August 17, 2015 at 5:40 am
very dumb question probably but what user details are you missing exactly ? Group 3 has only one user isn't it ?
August 17, 2015 at 6:27 am
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
August 19, 2015 at 4:00 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply