MySQL query with multiple inner joins on same table

Question

I have two tables : tblBooks and tblBookMeta

tblBookMeta holds the genres of the books, and there could be more than one.

Using this query I get all the results as shown.

SELECT * FROM tblBooks as B inner join tblBookMeta as BM ON B.BookId = BM.BookId 

tblBooks                           tblBookMeta
BookId  Title                   |  BookMetaId   BookId  MetaName    MetaValue   
1       My Thriller Book        |  1            1       Genre       Thriller    
2       My SciFi Book           |  2            2       Genre       SciFi   
3       My Thriller SciFi Book  |  3            3       Genre       Thriller    
3       My Thriller SciFi Book  |  4            3       Genre       SciFi

To query the books by genre I can use this query

SELECT * FROM tblBooks as B
inner join tblBookMeta as BM ON B.BookId = BM.BookId 
WHERE MetaName = 'Genre' and MetaValue = 'Thriller'

This will return 'My Thriller Book' and 'My Thriller SciFi book. Fine

if I only want books which have both the Thriller genre and the SciFi genre, I am getting a bit stuck. I can do it like this, but I am wondering if there is a better way without sub-queries. I may in the future want to query on more than two genres, which would create another sub query.

SELECT * FROM tblBooks as B
inner join tblBookMeta as BM ON B.BookId = BM.BookId 
WHERE MetaName = 'Genre' and MetaValue = 'Thriller' 
and B.BookId in (SELECT B.BookId FROM tblBooks as B
    inner join tblBookMeta as BM ON B.BookId = BM.BookId 
    WHERE MetaName = 'Genre' and MetaValue = 'SciFi')

There are alternate ways of achieving this (such as an extra inner join on tblBookMeta in the top level query as suggested in the answer given) but I'm not sure if they are necessarily better, either in terms of performance or readability. This is about as good as you can get I think. — Rob Streeting
– Rob Streeting, Commented May 29, 2020 at 10:01

Zeppi · Accepted Answer · 2020-05-29 09:59:49Z

1

You can do something like and check with EXPLAIN ANALYZE the better solution.

SELECT 
    *
FROM 
  tblBooks as B inner join tblBookMeta as TH
    ON (B.BookId = TH.BookId and TH.MetaValue = 'Thriller')
  inner join tblBookMeta as SC
    ON (B.BookId = SC.BookId and SC.MetaValue = 'SciFi')
WHERE 
    MetaName = 'Genre'

Regards

answered May 29, 2020 at 9:59

Zeppi

1,2359 silver badges12 bronze badges

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

1 Comment

StripyTiger Over a year ago

Thanks - this is much cleaner

Collectives™ on Stack Overflow

MySQL query with multiple inner joins on same table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related