Get a specific value returned when getting duplicate records using inner join oracle sql-CodePudding

I Have a members table and a roles table

roles table contain the roles id associated with each member number

each member may have multiple rolesId (only 1,2 in this case)

for example

member 1 may have roles 1,2

member 2 may have role 1

member 3 may have role 2

I'm trying to write a query to get the members and the roleId associated with the member, but if the member has multiple rows such as member 1, i want it to return only one record containing member 1 and role 2

if the member only have 1 roleId then it just return that roleId

this is what i have for the query, but its currently returning two records for each member that have two rolesId

                       SELECT m.member_id, r.role_id
                        FROM members m
                        INNER JOIN roles r ON m.MEMBER_NO =  r.MEMBER_NO

returns

member_id role_id

1 1

1 2

i want it to only return the second row

CodePudding user response：

Looks like

select m.member_id, 
       max(r.role_id)
from members m join roles r on m.member_no = r.member_no
group by m.member_id

CodePudding user response：

You can get all you might need in this single SQL using LISTAGG analytic function. WITH clause is just for sample data creation and is not part of the answer. Regards..

WITH
    members AS
        (
            SELECT 1 "MEMBER_NUMBER", 'Member 1 Name' "MEMBER_NAME" FROM DUAL UNION ALL
            SELECT 2 "MEMBER_NUMBER", 'Member 2 Name' "MEMBER_NAME" FROM DUAL UNION ALL
            SELECT 3 "MEMBER_NUMBER", 'Member 3 Name' "MEMBER_NAME" FROM DUAL UNION ALL
            SELECT 4 "MEMBER_NUMBER", 'Member 4 Name' "MEMBER_NAME" FROM DUAL UNION ALL
            SELECT 5 "MEMBER_NUMBER", 'Member 5 Name' "MEMBER_NAME" FROM DUAL 
        ),
    roles AS
        (
            SELECT 1 "ROLE_ID", 'member1 1st' "ROLE_NAME", 1 "MEMBER_NUMBER" FROM DUAL UNION ALL
            SELECT 2 "ROLE_ID", 'member1 2nd' "ROLE_NAME", 1 "MEMBER_NUMBER" FROM DUAL UNION ALL
            SELECT 1 "ROLE_ID", 'member2 1st' "ROLE_NAME", 2 "MEMBER_NUMBER" FROM DUAL UNION ALL
            SELECT 1 "ROLE_ID", 'member3 1st' "ROLE_NAME", 3 "MEMBER_NUMBER" FROM DUAL UNION ALL
            SELECT 2 "ROLE_ID", 'member3 2nd' "ROLE_NAME", 3 "MEMBER_NUMBER" FROM DUAL UNION ALL
            SELECT 3 "ROLE_ID", 'member3 3rd' "ROLE_NAME", 3 "MEMBER_NUMBER" FROM DUAL UNION ALL
            SELECT 1 "ROLE_ID", 'member4 1st' "ROLE_NAME", 4 "MEMBER_NUMBER" FROM DUAL UNION ALL
            SELECT 2 "ROLE_ID", 'member4 2nd' "ROLE_NAME", 4 "MEMBER_NUMBER" FROM DUAL UNION ALL
            SELECT 3 "ROLE_ID", 'member4 3rd' "ROLE_NAME", 4 "MEMBER_NUMBER" FROM DUAL UNION ALL
            SELECT 1 "ROLE_ID", 'member5 1st' "ROLE_NAME", 5 "MEMBER_NUMBER" FROM DUAL 
        )
--  -------------------------------------------------------------------------------------------
Select
    m.MEMBER_NUMBER "MEMBER_NUMBER",
    m.MEMBER_NAME "MEMBER_NAME",
    Min(r.ROLE_ID) "MIN_ROLE_ID",
    Max(r.ROLE_ID) "MAX_ROLE_ID",
    LISTAGG(r.ROLE_ID, ', ') WITHIN GROUP (Order By r.ROLE_ID) "LIST_OF_ROLE_IDS",
    LISTAGG(r.ROLE_NAME, ', ') WITHIN GROUP (Order By r.ROLE_ID) "LIST_OF_ROLE_NAMES"
From
    members m
Inner Join
    roles r ON(r.MEMBER_NUMBER = m.MEMBER_NUMBER)
Group By 
    m.MEMBER_NUMBER,
    m.MEMBER_NAME
Order By
    m.MEMBER_NUMBER
--  --------------------------------------------------------------------
--  R e s u l t
--
--  MEMBER_NUMBER MEMBER_NAME   MIN_ROLE_ID MAX_ROLE_ID LIST_OF_ROLE_IDS
--  ------------- ------------- ----------- ----------- ----------------
--              1 Member 1 Name           1           2 1, 2            
--              2 Member 2 Name           1           1 1               
--              3 Member 3 Name           1           3 1, 2, 3         
--              4 Member 4 Name           1           3 1, 2, 3         
--              5 Member 5 Name           1           1 1