CODEWITHSUNDEEP
sqlmariadb
Ramki
Ramki

Reputation: 472

How to avoid multiple joining the same table?

GROUPS

id team1_id team2_id
1     1       2
2     3       1

TEAMS

id  name
1   aaa
2   bbb
3   ccc

Is it possible to avoid the multiple join without changing schema or must the schema change to improve this? There will only ever be 2 teams per group.

Expected :

id team1_name team2_name
1     aaa       bbb
2     ccc       aaa

Upvotes: 1

Views: 1304

Answers (2)

Himanshu
Himanshu

Reputation: 3970

Try this

       Select id, max(case when 
       g.id=t.team1_id then
       g.name end), 
       max(case when 
       g.id=t.team2_id then
       g.name end)

      From groups g right join
     Teams t on
     g.id=t.id

Upvotes: 1

GMB
GMB

Reputation: 222632

One way to avoid multiple joins is to do conditional aggregation:

select
    g.id,
    max(case when t.id = g.team1_id then t.name end) team1_name,
    max(case when t.id = g.team2_id then t.name end) team2_name
from groups g
inner join teams t on t.id in (g.team1_id, g.team2_id)
group by g.id

While this makes it easier to extend the query if new columns are added to the groups table, this is actually not guaranteed to perform better than the join solution. This would depend on the distribution of yuour data. If performance matters to you, you would need to test both solutions against your real data.

Another key difference with the join solution is that this query does not actually ensure that the team ids that are part of the groups table are all available in the teams table (you would need to add an having clause to implement this). Depending on your use case, this might, or might not be what you want.

Upvotes: 1

Related Questions