Polymorphic association query multiple JOIN as separate columns

I’m trying to extract a query that needs to access a polymorphic association table in SQL twice (I need to JOIN it on both ‘types’)

Here is the structure for the SQL tables.

Answer: id, user_id
UserGroup (polymorphic): usergroupable_type, usergroupable_id, group_id
User (polymorphic column groupable_type: 'User'): id, name
AdminUser (polymorphic column groupable_type: 'AdminUser') : id, name
Group: id, name

I want to query each individual answer from the ‘Answer’ table, inner join with ‘User’ table on User.id == Answer.user_id, and inner join with ‘UserGroup’ table to get the ‘group_id’ for those with usergroupable_type == ‘User’ and usergroupable_id == User.id so that I can join to the ‘Group’ table to obtain the Group.name.

After that, I would like to obtain the AdminUser.name from the ‘AdminUser’ table by connecting with the polymorphic ‘UserGroup’ table on usergroupable_type == ‘AdminUser’.

Basically, I would like to obtain the Group.name, AdminUser.name per row in ‘Answer’ table.

Answer

If I understand right then something like

select g.name, au.name
from answer a
join usergroup ug on
    ug.usergroupable_id = a.user_id
    and ug.usergroupable_type = 'User'
join group g on
    g.id = ug.group_id
join usergroup aug on
    aug.group_id = g.id
    and usergroupable_type = 'AdminUser'
join adminuser au on
    au.id = aug.usergroupable_id

Should do it, but if I have misunderstood maybe share some sample data