How to replace many to many reference on join in MySQL?

Question

I create a simple data base on 3 tables: Author, that can has many Books, and Genre that also can has many Books.

I want to get all Genres of one Author using it authorId. I wrote next nested SQL select:

SELECT genre.id, genre.genre_id, genre.genre_name, genre.genre_descr FROM (SELECT book.genre_id FROM book WHERE book.author_id = 7654) AS b JOIN genre WHERE b.genre_id = genre.genre_id;

It works good for me, but is it possible some minimize this select? For example, get rid of nested select?

Yes, I know, that the good solution is to create reference many to many beetwen genre and author, but it will add some complications...

Thanks!

Abhijith Nagarajan · Accepted Answer · 2019-04-19T10:23:59.450

2

You are almost there. A simple join will do

SELECT DISTINCT g.* FROM genre g JOIN book b ON g.id = b.genre_id AND b.author_id = 7654

If you slow reads, then index on author_id should just work.

edited Apr 19 '19 at 10:23

answered Apr 19 '19 at 09:38

Abhijith Nagarajan

3,865
18
23

It works, but I need to return exectly genres objects, without joined values from book table to parse it in JpaRepository to Genre model. – Roman Shmandrovskyi Apr 19 '19 at 10:18
I have updated the answer – Abhijith Nagarajan Apr 19 '19 at 10:24

score 1 · Answer 2 · answered Apr 19 '19 at 09:41

1

One possible solution without using join:

select * from genre where id in (select genre_id from book where author_id = 7654)

answered Apr 19 '19 at 09:41

Amir Molaei

3,700
1
17
20

How to replace many to many reference on join in MySQL?

2 Answers2