How to show a data using JOIN in SQL (PHP)

Question

Select maintable.name FROM maintable

JOIN genres genre1 USING (tmdb_id)
  JOIN genres genre2 USING (tmdb_id)
WHERE genre1.genres_name = 'Action'
  AND genre2.genres_name = 'Drama'

group by maintable.name

Here genres is table name. genres_name is column name. genres1 and genres2 are just nor a table name, nor a column name, they are just random name in the code.

This is my code, now How do i display all genres_name?

The genres is like:

tmdb_id    genres_name
1             Action
1             Crime
1             Drama
2             Horror 
2             Comedy
2             Drama

The main table isl ike

tmdb_id      movie_title
1            The Dark Knight
2            Logan
3            Wonder Woman

Let me know, if you need more information. (Please do not ask to show, what i tried. Trust me, it will make the question more confusing)

I want to echo the genres like:

The Dark Knight - Drama, Action, Crime

Answer 1

Of course, you need to use group_concat:

Select maintable.movie_title, group_concat(genres.genres_name) AS genres_name
FROM maintable
JOIN genres USING (tmdb_id)
GROUP BY maintable.tmdb_id
HAVING find_in_set('Action', genres_name) AND find_in_set('Drama', genres_name)

See demo here.

Note: How does find_in_set works, please see official doc.

Answer 2

I would try something like this. But this is the best I can do guessing at it in my head... ( sorry for any mistakes )

$Sql = "SELECT
    m.name,
    GROUP_CONCAT( g.genres_name ) as genres_list
FROM
    maintable AS m
JOIN
    genres AS g USING (tmdb_id)
WHERE
   g.genres_name IN('Drama', 'Action')
GROUP BY m.tmdb_id";

MySQL GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group.

http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

Also note GROUP_CONCAT has a setting for the length, I don't recall what that is or how to change it, but it bit me in the butt one time. Basically it will truncate the list after a certain size, so be cautious of that.

See here: MySQL and GROUP_CONCAT() maximum length

AS I said I haven't tested this, but it seems you have a many to one relationship. Records in the maintable can have many related records in the genres table. Therefor, you should be able to group them on that relationship. Normally this would return 1 record for each pair ( same record in main table different in genre ) Without the group. The Group Concat allows you to compress that into a comma separated list.