Joining two tables on a third table

Question

If I have two tables that do not have a direct relationship but must be joined via a third table what would be the best method?

artist
======
 - id
 - name

event
======
 - id
 - title
 - start_time 

artist_event
======
 - artist_id 
 - event_id 

Would query below work to find all artists with an event in december?

select distinct a.name
from artist a
join artist_event ae
on a.id = ae.artist_id
join event e 
on e.id = ae.event_id
where date_trunc('month', e.start_time) = 'December'

Thanks

Answer 1

Normally, you want to include a year, when talking about a month. So I would recommend:

select distinct a.name
from artist a join
     artist_event ae
     on a.id = ae.artist_id join
     event e 
     on e.id = ae.event_id
where e.start_time >= '2019-12-01' and
      e.start_time < '2020-01-01';

Your version won't work because 'December' is a string (that happens to be a month name). date_trunc() returns a date (which is not a string).

That said, I would recommend exists instead:

select a.name
from artist a
where exists (select 1
              from artist_event ae join
                   event e 
                   on e.id = ae.event_id
              where a.id = ae.artist_id 

and e.start_time >= '2019-12-01' and e.start_time < '2020-01-01' );

This eliminates the duplicate elimination in the outer query.