I want to join users table with both Groupid and superadmingroupid from group table. Superadmingroupid may be null
I tried below query but not working
SELECT U.Name, G.Name
FROM Groups G
INNER JOIN USERS U ON G.Groupid = U.Gid
LEFT JOIN USERs U2 On G.superadmingroupid= U.Gid
where U.Name='Mishrsa'
Group table
Groupid Gname SuperAdminGroupId
----- ------ --------
17 A 3
2 B null
3 C null
Users
------
id Name Gid
-- ------- ----
1 mishra 2
2 mishrsa 3
I want to diplay the user with groups that are referenced as groupid or superadmingroupid Ex: User does not have groupid 17 but superadmingroupid 3 is there in users table so group 17 should come in the output
Output
Name GName
Mishra B
Mishra C
Mishra A
CodePudding user response：
I believe you should use UNION for that. (Maybe this is not the most elegant way).
The first part will give you the match between Groupid to Gid.
The second part will give you the match between SuperAdminGroupId to Gid.
The order is different then what you mentioned, and I do not know if it is important for you, but please try the below example:
SELECT U.Name, G.Name
FROM Groups G
JOIN Users U ON G.Groupid = U.Gid
UNION
SELECT U.Name, G.Name
FROM Groups G
JOIN Users U ON G.SuperAdminGroupId = U.Gid
CodePudding user response：
Solution for your problem is:
SELECT U.Name, G.GName
FROM Groups G
INNER JOIN USERS U
ON G.Groupid = U.Gid
OR G.superadmingroupid= U.Gid;
Working example: dbfiddle Link
CodePudding user response：
Posting this answer just because I'd already written it before Bogner Boy posted their answer.
I changed the table names a touch because GROUP is a reserved word in SQL Server.
Bonger Boy's UNION might be more efficient for larger tables, but for smaller tables you'll be fine to use an OR or an IN:
CREATE TABLE AdminGroup
(
GroupId INTEGER,
Gname CHAR(1),
SuperAdminGroupId INTEGER
);
CREATE TABLE Users
(
Id INTEGER,
Name NVARCHAR(64),
Gid INTEGER
);
INSERT INTO Users (Id, Name, Gid) VALUES (1, 'mishra', 2);
INSERT INTO Users (Id, Name, Gid) VALUES (2, 'mishra',3);
INSERT INTO AdminGroup (GroupId, Gname, SuperAdminGroupId) VALUES (17, 'A', 3);
INSERT INTO AdminGroup (GroupId, Gname, SuperAdminGroupId) VALUES (2, 'B', null);
INSERT INTO AdminGroup (GroupId, Gname, SuperAdminGroupId) VALUES (3, 'C', null);
SELECT U.Name, G.GName
FROM Users U
INNER JOIN AdminGroup G ON U.Gid = G.SuperAdminGroupId OR U.Gid = G.GroupId;
--INNER JOIN AdminGroup G ON U.Gid IN (G.SuperAdminGroupId, G.GroupId);
Here's a DBFiddle: