I have a books database.

CREATE TABLE `books` (
  `book_id` int(11) NOT NULL,
  `book_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `book_subject` int(11) DEFAULT NULL COMMENT 'What genre the book is to be picked up from subject table',
  `book_subject2` int(11) DEFAULT NULL COMMENT 'What genre the book is to be picked up from subject table',
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subject` (
  `subject_id` int(11) NOT NULL,
  `subject_name` varchar(100) NOT NULL

Let's say a book xyz falls under two subjects 'Mystery' and 'Thriller' with id values 1,2 in 'subject' table.

How do I write a select query that fetches the 'subject_name' value for both 'book_subject' and 'book_subject2'

  • Now I am stuck at second step. How do I get a total number of books from both the fields. Let's say XYZ book has 'Mystery' and 'Thriller' as subject 'book_subject' and 'book_subject2'. Another book ABC has 'Thriller' and 'Romance' as 'book_subject' and 'book_subject2'. How do I show the total count of 'Mystery' as 1, 'Thriller' as 2 and 'Romance' as 1? – Bipin Gaur Jul 15 '18 at 6:57
  • Don't use MyISAM; use InnoDB. And have a PRIMARY KEY on each table. – Rick James Jul 15 '18 at 15:22
  • Can you please help me with the sql query? I have book_id and subject_id are primary keys already – Bipin Gaur Jul 15 '18 at 18:18
  • I augmented my Answer. – Rick James Jul 15 '18 at 18:45

You just need to join on the same table twice:

select books.book_id, books.book_name, 
subject1.subject_name as sub1, subject2.subject_name as sub2
from books
join subject as subject1 on subject1.subject_id = book_subject 
join subject as subject2 on subject2.subject_id = book_subject2 ;

Have a bridging table:

create table book_subject_mapping
  `book_id` int(11) NOT NULL,
  `book_subject` int(11) NOT NULL 

... and use it to join. More flexible

  • It worked, thank you so much, after only 4 hours of searching - stack exchange and it's senior members to the rescue. I would really appreciate if you could guide me to an example or tutorial or implementation of the EAV model you wrote about above. – Bipin Gaur Jul 14 '18 at 17:19
  • I wouldn't call this an EAV model, but simply a many-to-many relationship. – Colin 't Hart Jul 15 '18 at 7:41

Just do two joins, one for each subject.

       FROM books b
            LEFT JOIN subject s1
                      ON s1.subject_id = b.book_subject
            LEFT JOIN subject s2
                      ON s2.subject_id = b.book_subject2;
  • And indexes?... – Rick James Jul 15 '18 at 15:22

It is messy and tedious. You would need a UNION or OR, plus worry about NULL.

It would be better to have a separate table relating book_id and book_subject, and allow any number of rows in that table. This has the benefit of allowing more than a fixed "2".


SELECT  book_subject,
        SUM(ct) as ct
    FROM  ( 
            ( SELECT  book_subject, COUNT(*) AS ct
                    FROM  books
                    GROUP BY  book_subject )
            UNION  ALL
            ( SELECT  book_subject2, COUNT(*)
                    FROM  books
                    GROUP BY  book_subject2 ) 
          ) AS s
    GROUP BY  book_subject;
    WHERE  book_subject IS NOT NULL

That gets the counts by id; then another JOIN is needed to get the names.

Or, if you avoid splaying an array (subjects) across columns...

I would not have a mapping table, but simply a tagging table (1:many instead of many:many)

create table subjects
  `book_id` int(11) NOT NULL,
  `subject_name` VARCHAR(99) NOT NULL 

SELECT  subject_name,
        COUNT(*) as ct
    FROM  subjects
    GROUP BY  subject_name;
  • Thank you for your reply, however I am getting this error "[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM ( SELECT book_subject2, COUNT(*) ' at line 10 " – Bipin Gaur Jul 16 '18 at 1:45
  • Oops, a typo. I hope there aren't more. – Rick James Jul 16 '18 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.