Postgres Join Query for a table referencing itself-CodePudding

I have a table badges with the following schemas

create table badges (
 id, serial primary key,
 title, text not null,
 next_badge_id: int references badges,
 .
 .
)

Given table as:

id |      title       | next_badge_id
---- ------------------ ---------------
  1 | Bronze Partner   |             2
  2 | Silver partner   |             3
  3 | Gold partner     |             4
  4 | Diamond partner  |             5
  5 | Platinum partner |

How do I write a query to return this:

id |      title       | next_badge
---- ------------------ ---------------
  1 | Bronze Partner   | Silver partner
  2 | Silver partner   | Gold partner
  3 | Gold partner     | Diamond partner 
  4 | Diamond partner  | Platinum partner
  5 | Platinum partner |

CodePudding user response：

You can use left join query

Sample data and query structure: dbfiddle

select
  b1.id,
  b1.title,
  b2.title as next_badge
from
  badges b1
  left join badges b2 on b1.next_badge_id = b2.id