CODEWITHSUNDEEP
sqlsql-server
user2771738
user2771738

Reputation: 951

Sql server join instead union

I have relation that agent can have many policies. Certain agents are in role 'MAIN' agents and some of them are in role 'SUBAGENTS'.

I am trying to find all agents assigned to same policies as main agent x or sub agent y.

Is there way to avoid union in such cases?

I have extracted common part in with statement. Now few policies should be marked as MAIN and few as SUB.

with active_agents_on_policy as
(
select po.code, ag.number
from policy as po
inner join agent as ag on po.id = ag.policy_id
where ag.status = 'active'
and po.status = 'active'
)

select *, 'MAIN' from active_agents_on_policy tmp1 inner join active_agents_on_policy tmp2 on tmp1.code = tmp2.code
where tmp1.agent_id = '112'

UNION

select *, 'SUB' from active_agents_on_policy tmp1 inner join active_agents_on_policy tmp2 on tmp1.code = tmp2.code
where  tmp1.agent_id = '634'

This statement return proper result. But I think it is not optimized. Is there any way to make it faster?

Upvotes: 0

Views: 58

Answers (2)

LukStorms
LukStorms

Reputation: 29647

So, the agents assigned to same policies?

Then maybe the self-join of the CTE should be on the policy_id, instead of the policy's code?

WITH ACTIVE_AGENTS_ON_POLICY AS
(
  SELECT 
   po.id AS policy_id,
   po.code,
   ag.agent_id,
   ag.number
  FROM policy AS po
  JOIN agent AS ag 
    ON ag.policy_id = po.id 
   AND ag.status = 'active'
  WHERE po.status = 'active'
)
SELECT aa1.policy_id
, aa1.code AS policy_code1
, aa2.code AS policy_code2
, aa1.number AS agent_number1
, aa2.number AS agent_number2
, CASE 
  WHEN aa1.agent_id = '112' THEN 'MAIN'
  WHEN aa1.agent_id = '634' THEN 'SUB'
  END AS [Type]
-- , aa1.agent_id AS agent_id1
-- , aa2.agent_id AS agent_id2
FROM ACTIVE_AGENTS_ON_POLICY AS aa1 
LEFT JOIN ACTIVE_AGENTS_ON_POLICY AS aa2 
  ON aa2.policy_id = aa1.policy_id
WHERE aa1.agent_id IN ('112', '634')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269783

IN and CASE come to mind:

select *,
       (case when tmp1.agent_id in (112) then 'MAIN' else 'SUB' END)
from active_agents_on_policy tmp1 inner join
     active_agents_on_policy tmp2
     on tmp1.code = tmp2.code
where tmp1.agent_id in (112, 634)

Note: I removed the single quotes around the numbers. I assume these are actually numbers and not strings. If they are strings, then keep the single quotes.

Upvotes: 1

Related Questions