Select where Count less than 10 where column names are from 3 joined tables

Question

I have three tables venue, concert and event. I am looking for a way of showing all details of venues which have been allocated less than two events including those with no allocations. Event table gets details of venue indirectly from concert table using a join, No foreign in event for Venues but there is one for concert.

Below is a code but it doesn't work as expected

 SELECT v.venue_id, v.venue_name, COALESCE( x.cnt, 0 ) Venue_count FROM 
 venue v
 LEFT JOIN concert ct ON v.venue_id = ct.venue_id
 LEFT JOIN event e ON e.concert_id = ct.concert_id
 LEFT OUTER JOIN (SELECT concert_id, COUNT( * ) cnt
 FROM event
 GROUP BY concert_id )x ON ct.concert_id = x.concert_id
 GROUP BY Venue_count
 ORDER BY  `Venue_count` DESC

Answer 1

I think your query can be written in an easier way

select  v.venue_id, v.venue_name
from    venue v
left join
        concert ct
on      v.venue_id = ct.venue_id
left join
        event e
on      e.concert_id = ct.concert_id
group by v.venue_id, v.venue_name
having   count(distinct e.event_id) < 2

This will go from venues to concerts and from concerts to events, counting the number of events for each venue and returning only those with less than two. Venues with no allocations will be preserved by the usage of left join.