How to select two fields from a table using join with another table 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
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


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'
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".

Counting

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;