CODEWITHSUNDEEP
sqljoinconditional-statements
pter
pter

Reputation: 55

Conditional SQL JOIN based on column contents

I have a table, event_sessions that contains a session_submitted_by ID and a session_submitter_type. I need to JOIN session_submitted_by to one of two different tables, based on the contents of session_submitter_type.

I can't just do an LEFT OUTER JOIN on both tables and choose whichever returns content because both tables may contain an ID match.

Here is my improper SQL/pseudo-code that shows what I'm trying to accomplish:

   SELECT es.event_session_id,
          subu.first_name + ' ' + subu.last_name as submitted_by_name,
          subu.email as submitter_email
     FROM event_session es
CASE WHEN es.session_submitter_type = 'speaker'
     THEN
         LEFT OUTER JOIN speakers subu 
         ON subu.speaker_id = es.session_submitted_by                       
    ELSE
        LEFT OUTER JOIN users subu 
        ON subu.user_id = es.session_submitted_by                            
    END

Any suggestions how to code this without having to resort to a UNION?

Upvotes: 0

Views: 115

Answers (1)

enl8enmentnow
enl8enmentnow

Reputation: 943

Interesting question. I would do it like so:

SELECT es.event_session_id,
      coalesce(spk.first_name, usr.first_name) 
      + ' ' + coalesce(spk.last_name, usr.last_name) as submitted_by_name,
      coalesce(spk.email, usr.email) as submitter_email
 FROM event_session es
     LEFT OUTER JOIN speakers spk 
     ON es.session_submitter_type = 'speaker' and spk.speaker_id = es.session_submitted_by                       
     LEFT OUTER JOIN users usr 
     ON es.session_submitter_type <> 'speaker' and usr.user_id = es.session_submitted_by

You basically use two left outer joins, and one only one of them is ever going to be active for any given row of event_session.

Note: If session_submitter_type is nullable then you need to add a NULL check in the second join to maintain the semantics of your pseudo code.

Upvotes: 2

Related Questions