Oracle fpull out duplicate data between two table-CodePudding

Table emp :

UserId	Name
1	Ken
3	John
5	Gary
7	May
9	Simon

Table role :

UserId	Role
1	Staff
3	Staff
3	Hr
5	Hr
7	Hr
7	Staff
9	Hr

When the UserId appear two times in Table Role means the person are having two roles. Then extract the Name inthe Table EMP and below is my expected result. How ca I do it？

Expected result :

Name
John
May

Here is the code I currently have. I can only selected from Table Role instead of the name from EMP.

SELECT UserId, COUNT(*) AS Duplicate
  FROM Role
HAVING COUNT(*)>1

CodePudding user response：

Yet there are another options ;

by using IN operator :

SELECT Name
  FROM emp e
 WHERE e.UserId IN ( SELECT UserId FROM role GROUP BY UserId HAVING COUNT(*)>1 )

by using EXISTS(even without need of a GROUP BY clause) :

SELECT Name
  FROM emp e
 WHERE EXISTS ( SELECT 1 FROM role WHERE UserId = e.UserId HAVING COUNT(*)>1 )

Demo

CodePudding user response：

WITH
    emp AS
        (
            SELECT 1 "USER_ID", 'Ken' "U_NAME" FROM DUAL UNION ALL
            SELECT 3 "USER_ID", 'John' "U_NAME" FROM DUAL UNION ALL
            SELECT 5 "USER_ID", 'Gary' "U_NAME" FROM DUAL UNION ALL
            SELECT 7 "USER_ID", 'May' "U_NAME" FROM DUAL UNION ALL
            SELECT 9 "USER_ID", 'Simon' "U_NAME" FROM DUAL 
        ),
    role AS
        (
            SELECT 1 "USER_ID", 'Staff' "ROLE" FROM DUAL UNION ALL
            SELECT 3 "USER_ID", 'Staff' "ROLE" FROM DUAL UNION ALL
            SELECT 3 "USER_ID", 'Hr' "ROLE" FROM DUAL UNION ALL
            SELECT 5 "USER_ID", 'Hr' "ROLE" FROM DUAL UNION ALL
            SELECT 7 "USER_ID", 'Hr' "ROLE" FROM DUAL UNION ALL
            SELECT 7 "USER_ID", 'Staff' "ROLE" FROM DUAL UNION ALL
            SELECT 9 "USER_ID", 'Hr' "ROLE" FROM DUAL
        )   
        
SELECT 
    e.U_NAME "NAME"
FROM emp e
INNER JOIN role r ON (r.USER_ID = e.USER_ID)
GROUP BY e.U_NAME
HAVING Count(*) > 1
--  
--  R e s u l t
--
--  NAME
--  -----
--  John  
--  May