SQL query to select columns from multiple tables with conditions on Group By-CodePudding

I have 3 Tables with relationships:

TableA:

Party_Number    Account_Number    Email_Code    Relation_Code
1111            A00071            null          B
1111            A00071            null          C
1111            A00071            null          D
1111            A00072            140           D
1111            A00073            140           C
1111            A00074            140           C
1111            A00075            null          B

TableB:

Account_Number    Date
A00071            8/8/2020
A00072            null
A00073            null
A00074            null
A00075            null

TableC:

Party_Number    Email
1111            [email protected]

I need to join 3 tables to get the following result (only records where "Relation_Code" is 'C' or 'D'):

Party_Number    Account_Number    Email_Code    Relation_Code    Date       Email
    1111            A00071            null          C            8/8/2020   [email protected]
    1111            A00071            null          D            8/8/2020   [email protected]
    1111            A00072            140           D            null       [email protected]
    1111            A00073            140           C            null       [email protected]
    1111            A00074            140           C            null       [email protected]

I wrote this query to get the result:

Select A.Party_Number, A.Account_Number, A.Relation_Code, A.Email_Code,
       B.Date, C.Email
       from TableA A, TableB B, TableC C
       Where A.Account_Number= B.Account_Number
       AND A.Party_Number = C.Party_Number
       AND A.Relation_Code in ('C','D')
 Order By A.Account_Number

But there can be rows with same Account_Number, but different Relation_Code ('C' and 'D'). For ex (A00071).

If there are duplicate Account_Number, I need to select only Account_Number where Relation_Code is 'D'.

How do I write a SQL query to join multiple tables and group by a condition?

CodePudding user response：

Use standard joins! This helps formulating the logic and separating the joining conditions from other filtering predicates.

Here, it seems like, starting from a filtered on column relation_code, you want to allow "missing" relationships in b and c. We would phrase it with left joins:

select a.party_number, a.account_number, a.relation_code, a.email_code,
    b.date, c.email
from tablea a
left join tableb b on b.account_number = a.account_number
left join tablec c on c.party_number   = a.party_number
where a.relation_code in ('C','D')
order by a.account_number

If there are duplicate Account_Number, I need to select only Account_Number where Relation_Code is 'D'.

For this, we could use window functions to pre-filter a:

select a.party_number, a.account_number, a.relation_code, a.email_code,
    b.date, c.email
from (
    select a.*, 
        row_number() over(partition by account_number order by relation_code desc) rn 
    from tablea a
    where relation_code in ('C','D')
) a
left join tableb b on b.account_number = a.account_number
left join tablec c on c.party_number   = a.party_number
where a.rn = 1
order by a.account_number