[ACCEPTED]-SQL Conditional / Case Joining / Polymorphic Associations?-polymorphic-associations

Accepted answer
Score: 10

You can't make the join table conditional, so 12 in this case you would have to join events 11 to both users and organisations and use 10 coalesce to merge the common fields (eg. name) together.

select e.id, coalesce(u.name, o.name) owner_name
from events e
left join users u on e.owner_id = u.id and e.owner_type = 'user'
left join organisations o on e.owner_id = o.id and e.owner_type = 'org'

However, you 9 may consider creating an owners table, which 8 contains both users and organisations, with 7 a structure like (id, type, org_id, name, ...). This 6 would only require a single join, but may 5 complicate other areas of your schema, eg. user 4 membership of an organisation.

An alternative 3 method would be to union the users and organisations 2 tables together and then join once from 1 events.

Score: 2


  • Owner has columns common to all owner-subtypes.
  • Person and Organization have columns specific to each one.


Score: 0

How about moving ownership information out 6 of Events into two join tables, EventsUsers and EventsOrganisations (each of 5 which has just two columns, FKs to Events and 4 the apppropriate owning-object table) ? Then 3 you can UNION two queries each of which joins 2 through the join table to the owning-object 1 table.

Score: 0

A bit old, but I think it would be useful, this 3 version performs better in my scenario than 2 multiple joins

select e.id,
    case when e.owner_type = 'person' then
            select p.name from person p
            where p.id=e.owner_id
            select o.name from organization o
            where o.id=e.owner_id
    end entityName,
from events e

in postgres you could even 1 build a json of entire related entity

More Related questions