Problems integrating a subquery that uses GROUP_CONCAT

Hi guys!

I’m creating an alphabetical list of hotels, each of which are assigned to one or more clients. I wish to display this list with a comma separated list within a column of each client any given hotel is assigned to.

So far, I have a query for the alphabetical list of hotels…

SELECT


    venues.venue_id AS id,
    DATE_FORMAT(venues.creation_date, '%a %D %b %y') AS creation_date,
    venues.name,
    venues.town_city,
    venues.county,


    venues_clients.mode


FROM
    venues
INNER JOIN
    venues_clients
WHERE
    (venues.client_id = venues_clients.owner_id)
AND
    (venues.venue_id = venues_clients.venue_id)
AND
    (venues.name LIKE '" . $this->db->escape_like_str($array_parameters['character']) . "%')
GROUP BY id
ORDER BY name ASC

I also have a query that retrieves a list of hotels and to which clients they’re assigned…

SELECT


    GROUP_CONCAT(clients.name) AS name,
    venues.venue_id


FROM
    clients
INNER JOIN
    venues_clients
ON
    (clients.client_id = venues_clients.client_id)
INNER JOIN
    venues
ON
    (venues.venue_id = venues_clients.venue_id)
GROUP BY
    venues_clients.venue_id

I need to combine the two, but so far I’ve drawn a blank at every attempt.

Any help would be greatly appreciated!

SELECT venues.venue_id AS id
     , DATE_FORMAT(venues.creation_date, '%a %D %b %y') AS creation_date
     , venues.name
     , venues.town_city
     , venues.county
     , venues_clients.mode
     , GROUP_CONCAT(clients.name) AS clients
  FROM venues
INNER 
  JOIN venues_clients
    ON venues_clients.venue_id = venues.venue_id
   AND venues_clients.owner_id = venues.client_id
INNER
  JOIN clients
    ON clients.client_id = venues_clients.client_id   
 WHERE venues.name LIKE '" . $this->db->escape_like_str($array_parameters['character']) . "%')
GROUP 
    BY id
ORDER 
    BY name ASC

You know, I can only buy your book so many times!

Again, thank you.