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

Questions

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

December 8, 2022

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            abc@gmail.com

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   abc@gmail.com
    1111            A00071            null          D            8/8/2020   abc@gmail.com
    1111            A00072            140           D            null       abc@gmail.com
    1111            A00073            140           C            null       abc@gmail.com
    1111            A00074            140           C            null       abc@gmail.com

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?

>Solution :

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