Home / Questions / 368534
Accepted
sirdaiz
Asked: 2020-06-26 06:36:55 +0800 CST 2020-06-26 06:36:55 +0800 CST

Get result from a many-to-many table

  • 772

Suppose I have the following tables

"AGENT-DIVISION"

DIVISION_ID   AGENT_ID
102         1
104         1
102         50
4           100

"AGENT-ROLE"

ROLE_ID   AGENT_ID
2           1
4           1
2           50
4           50
4           100

I would like to obtain all the divisions that have roles 2 and 4, in this example it would return the divisions with id 102 and 104

I have tried these queries:

select division_id, ar.role_id from agent_division d 
INNER JOIN agent a on d.agent_id = a.id 
inner join agent_role ar on ar.agent_id = a.id and ar.role_id in (2,4) 
group by division_id, ar.role_id 
having count(distinct ar.role_id) = 2;


select d.division_id, ar.role_id from agent_division d,  agent a,  agent_role ar 
where d.agent_id = a.id and ar.agent_id = a.id and ar.role_id in (2,4) 
group by d.division_id, ar.role_id having count(distinct ar.role_id) = 2;

Any suggestion?

sql

2 Answers

  1. Best Answer

    What you can do is group by agent_idand show only the ones that have two roles:

    select agent_id
       from agent_role 
       where role_id in (2,4)
       group by agent_id
       having count(distinct role_id) = 2

    Integrated to the query of agent_division, you could be:

    select division_id
       from  agent_division
       where agent_id IN (select agent_id
                            from agent_role 
                            where role_id in (2,4)
                            group by agent_id
                            having count(distinct role_id) = 2)
    • 3

  2. As you only mention 2 ROLE_IDof your interest; I suggest the following:

    WITH VW_VISTACONTEO AS (
   SELECT ROLE_ID, AGENT_ID, COUNT(*) OVER(PARTICION BY AGENT_ID) TOTAL FROM AGENT_ROLE WHERE ROLE_ID IN (2,4)
   GROUP BY ROLE_ID, AGENT_ID
)
SELECT 
   DISTINCT AGENT_ID 
FROM 
   AGENT_ROLE
WHERE
   (ROLE_ID, AGENT_ID) IN (SELECT ROLE_ID, AGENT_ID FROM VW_VISTACONTEO WHERE TOTAL>1)

    We apply to the "view" created in WITHwhich it returns those repeated more than once (although the correct thing would be to explicitly put equal to 2), but just so that it serves as a guide there.

    • 0