Records are shown multiple times using left join

user_18943543 0
Viewed 21

I have data in three tables.

Table A:

id      name 
1001    foo 
1002    bar 
1003    abc 
1004    def

Table B:

A_ID    group_index C_ID
1001    1           5001
1001    2           5002
1001    3           5003
1002    1           5001
1002    3           5003
1003    1           6001
1003    2           6002
1003    3           6003

Table C:

ID      group_ID
5001    101
5002    102
5003    103
6001    201
6002    202
6003    203

I need a single query to return:

id      name    group1  group2  group3 
1001    foo     101     102     103
1002    bar     101     null    103
1003    abc     201     202     203
1004    def     null    null    null

But when I run my failed attempt at a query:

SELECT id, name,
(SELECT C.group_ID From C Where 
A.id = B.A_ID AND       
B.group_index=1 AND         
B.C_ID = C.ID) AS group1,
(SELECT C.group_ID From C Where 
A.id = B.A_ID AND       
B.group_index=2 AND         
B.C_ID = C.ID) AS group2,       
(SELECT C.group_ID From C Where 
A.id = B.A_ID AND       
B.group_index=3 AND         
B.C_ID = C.ID) AS group3
FROM A
LEFT JOIN B on A.ID=B.A_ID

The results are:

id      name    group1  group2  group3 
1001    foo     101     null    null
1001    foo     null    102     null
1001    foo     null    null    103
1002    bar     101     null    null
1002    bar     null    null    103
1003    abc     201     null    null
1003    abc     null    202     null
1003    abc     null    null    203
1004    def     null    null    null

Not sure if it's something with the LEFT JOIN or if I'm taking an entirely wrong approach. I would greatly appreciate guidance.

sql
1 Answers

That's how SQL works. In your sample data, tableb has 4 rows for id 1001, so without any aggregation your query is going to return 4 rows for 1001. You need to use in this case conditional aggregation:

select
a.id,
a.name,
max(case when b.group_index = 1 then group_id end),
max(case when b.group_index = 2 then group_id end),
max(case when b.group_index = 3 then group_id end)

from
a
left join b
    on a.id = b.a_id
left join c
    on b.c_id = c.id
group by
a.id,
a.name

fiddle

user_27515730
Related