I am hoping this is not a difficult question and hoping I explain this enough for someone to understand. In the query below, what I am trying to do is find an account(a.acct_no) where an account is on the ina table has 2 records. One record where one acct_no has an IRA_TYPE of 'IR' and one where a different account number has an IRA_TYPE of 'RH'. They also need to have the same tax_id which is why I joined it with the ACT_TABLE. I was able to use this query to find a similar tax id for 2 accounts that have IR and RH by scrolling through it until I landed on two records that met the requirements but I am trying to see how I can rewrite this query to only return one account coded 'IR' and one account coded 'RH' where the accounts have matching tax_ids.

SELECT a.acct_no, a.ira_type, b.tax_id
FROM INA a
inner join ACT_TABLE b on a.acct_no = b.acct_no
where a.ira_type in ('IR', 'RH')
group by b.tax_id, a.acct_no, a.ira_type
--having count(*) > 1
order by tax_id

Here is a sample of what I am scrolling though:

CodePudding user response：

Maybe this query can help:

SELECT b.tax_id, ir.acct_no as ir, rh.acc_no as rh
FROM ACT_TABLE b
LEFT JOIN INA ir ON b.acct_no = ir.acct_no AND ir.ira_type = 'IR'
LEFT JOIN INA rh ON b.acct_no = rh.acct_no AND rh.ira_type = 'RH'
WHERE ir.acct_no <> rh.acc_no

CodePudding user response：

This simple change may be usefull

SELECT max(a.acct_no) as a.acct_no, a.ira_type, b.tax_id
FROM INA a
inner join ACT_TABLE b on a.acct_no = b.acct_no
where a.ira_type in ('IR', 'RH')
group by b.tax_id, a.ira_type
order by tax_id

CodePudding user response：

This will may be help you :

SELECT a.acct_no, a.ira_type, b.tax_id FROM INA a
inner join ACT_TABLE b on a.acct_no = b.acct_no
where a.ira_type in ('IR', 'RH')
group by b.tax_id, a.ira_type
order by tax_id
OR
SELECT Max(a.acct_no), a.ira_type, b.tax_id FROM INA a
inner join ACT_TABLE b on a.acct_no = b.acct_no
where a.ira_type in ('IR', 'RH')
group by b.tax_id, a.ira_type
order by tax_id

CodePudding user response：

with data as (
    select b.tax_id, a.acct_no, a.ira_type,
        min(a.ira_type) over (partition by b.tax_id) as mn,
        max(a.ira_type) over (partition by b.tax_id) as mx,             
        row_number() over (partition by b.tax_id, a.ira_type order by a.acct_no) as rn
    from INA a inner join ACT_TABLE b
        on a.acct_no = b.acct_no and a.ira_type in ('IR', 'RH')
)
select tax_id, acct_no, ira_type
from data
where mn <> mx and rn = 1 /* make sure both types are represented and keep just one */
order by tax_id, ira_type;

or if you wanted them in the same row together:

select ir.tax_id, ir.acct_no as ir_acct_no, rh.acct_no as rh_acct_no
from
(
    select b.tax_id, min(a.acct_no) as acct_no, 'IR' as ira_type
    from INA a inner join ACT_TABLE b on a.acct_no = b.acct_no
    where a.ira_type = 'IR'
    group by b.tax_id
) as ir
inner join
(
    select b.tax_id, min(a.acct_no) as acct_no, 'RH' as ira_type
    from INA a inner join ACT_TABLE b on a.acct_no = b.acct_no
    where a.ira_type = 'RH'
    group by b.tax_id
) as rh
    on ir.tax_id = rh.tax_id;

https://dbfiddle.uk/F3J588GF