join distinct rows from one table with multiple matching rows from another

Question

I need to get all rows distinct from table a, and store any rows matching tmdb_id from table b stored as array in distinct result, but I'm just getting multiple returns for results from table a.

Basically, I have titles in one table (each row is unique), then genres in the other table (multiple rows might exist matching a given title from the titles table).

Is it possible to get all rows, distinct, from the title table, and add as an array the matching results from the genres table?

All I have is this:

$query = "SELECT DISTINCT a.*,b.tmdb_id AS genres FROM titles a JOIN genre_titles b ON b.title_id = a.tmdb_id ORDER BY a.sort_title ASC";
if($result = $mysqli->query($query)) $titles = mysqli_fetch_all($result, MYSQLI_ASSOC);

It's not giving me distinct results from titles if there's more than one matching genre found in genre_titles.

I'm getting output like this:

[0] => Array
    (
        [id] => 217
        [tmdb_id] => 353958
        [display_title] => Revolution
        [sort_title] => Revolution
        [genres] => 9648
    )

[1] => Array
    (
        [id] => 217
        [tmdb_id] => 353958
        [display_title] => Revolution
        [sort_title] => Revolution
        [genres] => 18
    )

where what I'm hoping to get is this:

[0] => Array
    (
        [id] => 217
        [tmdb_id] => 353958
        [display_title] => Revolution
        [sort_title] => Revolution
        [genres] => array
            (
               [0] => 9648
               [1] => 18
            )
    )

Is that possible just with mysql? If not I can just query all the genres separately and array_column them with genre_titles.title_id(=titles.tmdb_id) as key, for reference, but I'd like to do it in one query.

Answer 1

First of all, I hope I understand your question :).

I have the strong feeling you can do that by using the group_concat function

SELECT DISTINCT a.*, GROUP_CONCAT(b.tmdb_id) AS genres 
FROM titles a JOIN genre_titles b ON b.title_id = a.tmdb_id 
ORDER BY a.sort_title ASC
GROUP BY a.*

just in case, I'm not sure if the last line is correct, as I don't know if you can do a GROUP BY a.*. if this doesn't work, you can use a nested query that only returns the a.id (I'm assuming a has an id column`) and the genres, and then do a join between the nested subquery and a

SELECT t.*, g.genres
FROM titles t,
     (SELECT gt.title_id title_id, GROUP_CONCAT(gt.tmdb_id) genres
      FROM genre_titles gt
      GROUP BY gt.title_id) as g
WHERE t.id = g.title_id
ORDER BY t.sort_title ASC

I haven't tested the query myself, but I think it should work(t)(r)(c).