Beginner: Concatenate queries 2

[urielk]

This must be a very common task for you SQL programmers:

My users can belong to two groups... so I created a Table Users and Groups.

Users have two Group Ids (Foreighn keys) in a relationship with the Group Primary key, described next:

Table Users
PK UserId
FK GrpId1
Fk GrpId2
UserName

Table Group
PK GrpId
GrpName

Now I created 3 queries:
SELECT Group.GrpName AS Grp1Name
FROM Group INNER JOIN
Users ON Group.GrpId = Users.Grp1Id
WHERE (Users.UserId = @UserId)

SELECT Group.GrpName AS Grp2Name
FROM Group INNER JOIN
Users ON Group.GrpId = Users.Grp2Id
WHERE (Users.UserId = @UserId)

SELECT UserName, Grp1Id, Grp2Id
FROM Users
WHERE (UserId = @UserId)

How do I get a final record with
UserName, Grp1Id, Grp1Name, Grp2Id, Grp2Name

 

[BobWman]

Here is a possiblility - not tested

Select
U.UserName,
G1.Grp1Id,
G1.Grp1Name,
G2.Grp2Id,
G2.Grp2Name
From
Users U
Left Join Group G1 On G1.GrpID = U.GrpID1
Left Join Group G2 On G2.GrpID = U.GrpID2
Where
U.UserID = @UserID

Good luck

 

[bperry]

I would try something like this:

Select
u.username,
u.grpid1,
g1.grpname,
u.grpid2,
g2.grpname
From USERS u
LEFT JOIN Groups g1
ON u.Grpid1 = g1.GrpId
LEFT JOIN Groups g2
ON u.GrpId2 = g2.Grpid
where u.userid = @userid
----------------------------
If it is possible that sometimes a user has no group1 or group2 id, then you might want to add some checking for NULLs
----------------------------
Select
u.username,
ISNULL(u.grpid1,'-') as 'Grp1 Id',
ISNULL(g1.grpname,'') as 'Grp1 Name',
ISNULL(u.grpid2,'') as 'Grp2 Id',
ISNULL(g2.grpname,'') as 'Grp2 Name'
From USERS u
LEFT JOIN Groups g1
ON u.Grpid1 = g1.GrpId
LEFT JOIN Groups g2
ON u.GrpId2 = g2.Grpid
where u.userid = @userid
--------------------------------

 

[urielk]

Thx it works!