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

Please post all the table schema, and provide some sample data, and your expected result. — Blank
– Blank, Commented Jun 30, 2017 at 3:28
I showed how my table looks like above. And also my expected result. — Josh Poor
– Josh Poor, Commented Jun 30, 2017 at 3:29
Why are you joining 2x on the same table, but have 3x types? This isn't Mysql is it? — ArtisticPhoenix
– ArtisticPhoenix, Commented Jun 30, 2017 at 3:30
In MySQL I would use a correlated subQuery in the select with group_concat instead. — ArtisticPhoenix
– ArtisticPhoenix, Commented Jun 30, 2017 at 3:31
I love how I come up with the answer then every one jumps on the band wagon... lol ... just saying. — ArtisticPhoenix
– ArtisticPhoenix, Commented Jun 30, 2017 at 3:55

Blank · Accepted Answer · 2017-06-30 04:00:53Z

3

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.

edited Jun 30, 2017 at 4:00

answered Jun 30, 2017 at 3:49

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ArtisticPhoenix Over a year ago

I answered it 14 minutes ago, you 8 minutes, also I mentioned group concat 28 minutes ago in the comments, so yea basically.

ArtisticPhoenix Over a year ago

I cant be bothered with little things like "logic" and all that .. lol .. I was just saying. It's no big deal really. I did mine in my head, no testing. Besides I never used find_in_set before, I get that IN is like Action OR Drama ... and not AND, but whatever ... right logic and all that, cant spoon feed them.

ArtisticPhoenix Over a year ago

Thanks for the link, that was a good call on find_in_set I was just giving you a hard time.... lol ... need my entertainment tonight. I will have to remember that function, for future use.

John Doo Over a year ago

This one works, but it caused a big error. Pagination system is messed. If i use AND (both) Action & Drama. It show only 8 records per page (my limit is 10). And reset the offset to 0 whenever, i change the page.

John Doo Over a year ago

If i use OR (Action or Drama or both), then it shows 91 records per page instead of 10 And reset the offset to 0 whenever, i change the page. @forward sir

|

ArtisticPhoenix · Accepted Answer · 2017-06-30 05:06:00Z

1

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.

edited Jun 30, 2017 at 5:06

answered Jun 30, 2017 at 3:43

ArtisticPhoenix

21.7k2 gold badges27 silver badges38 bronze badges

14 Comments

Josh Poor Over a year ago

m is related to something or just random name?

ArtisticPhoenix Over a year ago

maintable AS m its an alias of maintable, because its shorter then typing maintable every time. Its just the first letter, that's how I do it typically, I cant be bothered with typing that all out all the time, I am a busy man.

ArtisticPhoenix Over a year ago

in other words you could do maintable AS x2 if you wanted but that makes less sense.

Josh Poor Over a year ago

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN('Drama', 'Action')

ArtisticPhoenix Over a year ago

Obviously this wont work 'SELECT ... IN('Action' ... )' try "SELECT ... IN('Action' ... )" check your quotes. That's not my fault. ( I'm assuming that's the cause ) as I don't put any quotes to make it a string.....

|

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related