SQL query to return duplicate rows for certain column, but with unique values for another column-CodePudding

I have written the query shown here that combines three tables and returns rows where the at_ticket_num from appeal_tickets is duplicated but against a different at_sys_ref value

select top 100 
    t.t_reference, at.at_system_ref, at_ticket_num, a.a_case_ref
from 
    tickets t, appeal_tickets at, appeals_2 a
where 
    t.t_reference in ('AB123','AB234') -- filtering on these values so that I can see that its working
    and t.t_number = at.at_ticket_num
    and at.at_system_ref = a.a_system_ref
    and at.at_ticket_num IN (select at_ticket_num
                             from appeal_tickets
                             group by at_ticket_num
                             having count(distinct at_system_ref) > 1)
order by 
    t.t_reference desc

This is the output:

t_reference  at_system_ref  at_ticket_num   a_case_ref
-------------------------------------------------------
    AB123       30838974      23641583      1111979010
    AB123       30838976      23641583      1111979010
    AB234       30839149      23641520      1111977352
    AB234       30839209      23641520      1111988003

I want to modify this so that it only returns records where t_reference is duplicated but against a different a_case_ref. So in above case only records for AB234 would be returned.

Any help would be much appreciated.

CodePudding user response：

with data as (
    <your query plus one column>
    case when
        min() over (partition by t.t_reference)
        <>
        max() over (partition by t.t_reference)
        then 1 end as dup
)
select * from data where dup = 1

CodePudding user response：

You want all ticket appeals that have more than one system reference and more than one case reference it seems. You can join the tables, count the occurrences per ticket and then only keep the tickets that match these criteria.

select *
from
(
  select
    t.t_reference, at.at_system_ref, at.at_ticket_num, a.a_case_ref,
    count(distinct a.a_system_ref) over (partition by at.at_ticket_num) as sysrefs,
    count(distinct a.a_case_ref) over (partition by at.at_ticket_num) as caserefs
  from tickets t
  join appeal_tickets at on at.at_ticket_num = t.t_number
  join appeals_2 a on a.a_system_ref = at.at_system_ref
) counted
where sysrefs > 1 and caserefs > 1
order by t.t_reference, at.at_system_ref, at.at_ticket_num, a.a_case_ref;