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!