CODEWITHSUNDEEP
mysqlsqljoindistinct
Maulana iman
Maulana iman

Reputation: 67

Join query but eliminate the duplicate

So I have this data, from tbl_left :

id   name   type    our_assets
1   'Mira'   cc        10
2   'Mira'   bb        9

And this one, from tbl_right :

id   name   assets_other  name_of_assets
1   'Mira'      3            assets_a
2   'Mira'      1            assets_b
3   'Mira'      1            assets_c
4   'Mira'      1            assets_d

How can I join the both table and result something like this :

 name   type    our_assets   assets_other  name_of_assets
'Mira'   cc        10            3            assets_a
'Mira'   bb        9             1            assets_b
'Mira'                           1            assets_c
'Mira'                           1            assets_d

I don't care if the column type and our_assets have duplicate value, I've tried using join but the result become 8 rows instead of 4 like :

 name   type    our_assets   assets_other  name_of_assets
'Mira'   cc        10            3            assets_a
'Mira'   cc        10            3            assets_a
'Mira'   bb        9             1            assets_b
'Mira'   bb        9             1            assets_b
'Mira'                           1            assets_c
'Mira'                           1            assets_c
'Mira'                           1            assets_d
'Mira'                           1            assets_d

If I use group by name_of_assets the column type only return the "cc" value.

Upvotes: 0

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think you want to left join on id and name:

select r.*, l.type, l.our_assets
from tbl_right r left join
     tbl_left l
     on l.id = r.id and l.name = r.name

Upvotes: 0

GMB
GMB

Reputation: 222492

You need to add another join criteria than the name to avoid duplicating the records. One option uses row_number() (available in MySQL 8.0):

select r.name, l.type, l.our_assets, r.assets_other, r.name_of_asset
from (select r.*, row_number() over(partition by name order by id) rn from tbl_right) l
left join (select l.*, row_number() over(partition by name order by id) rn from tbl_left) r
    on r.name = l.name and r.rn = l.rn

If records may be missing on both ends of the join, you can use union all and aggregation instead:

select 
    name,
    max(type) type,
    amx(our_assets) our_assets,
    max(assets_other) assets_other,
    max(name_of_asset) name_of_asset
from (
    select 
        name, 
        type, 
        our_assets, 
        null assets_other, 
        null name_of_asset, 
        row_number() over(partition by name order by id) rn 
    from tbl_left
    union all
    select 
        name, 
        null, 
        null, 
        assets_other, 
        name_of_asset, 
        row_number() over(partition by name order by id) rn 
    from tbl_right
) t
group by name, rn

Upvotes: 1

Related Questions