Ordering by the number of matches

115
November 17, 2019, at 9:40 PM

I have been doing some searching, but I can't seem to get my search function to order my results. The user is supposed to be able to check multiple checkboxes of subjects and the task is to list all books with those subjects and rank them by the number of matches. This is as far as I got with my query without it erroring out SELECT b.ISBN, b.TITLE FROM BOOKS b, BOOK_SUBJECT bs WHERE bs.ISBN = b.ISBN AND bs.SUBJECT_ID = 47 OR bs.SUBJECT_ID=48 ORDER BY

My tables are structured as so:

BOOK_SUBJECT
ISBN        SUBJECT_ID
----------------------
12345678A   47
123456FF88  47
12345678A   48
123456FF88  49
123456FF55  47
123456FF55  48
123456FF55  49
123456FF11  48
123456FF11  49
BOOKS
ISBN         TITLE                                                 PRICE
-----------------------------------------------------------------------------
12345678A   Web Design                                              12
123456FF88  Getting JSON parse codec error testing on new machine   88.42
123456FF55  Second Test                                             88
123456FF11  Image stitching distorted wrap with multiple images      7
SUBJECTS
SUBJECTID        SUBJECT
------------------------
47               Computer Science
48               Maths
49               Physics
81               Cooking

So for example if I search for "computer science" and "physics" it should return all of the books with those two matches first and descending. Thank you so much, new to stackoverflow

Answer 1

First, learn to use proper JOIN syntax.

Second, the problem with your query is the lack of parentheses . . . but proper JOIN syntax actually fixes that. I prefer IN:

SELECT b.ISBN, b.TITLE
FROM BOOKS b JOIN
     BOOK_SUBJECT bs 
     ON bs.ISBN = b.ISBN 
WHERE bs.SUBJECT_ID IN (47, 48);

This query should at least return in a reasonable timeframe. But you probably want books that have both subjects rather than either subject. If so, use aggregation. Assuming that the subjects are not repeated for a single book:

SELECT b.ISBN, b.TITLE
FROM BOOKS b JOIN
     BOOK_SUBJECT bs 
     ON bs.ISBN = b.ISBN 
WHERE bs.SUBJECT_ID IN (47, 48)
GROUP BY b.ISBN, b.TITLE
HAVING COUNT(*) = 2;

Or, to rank them by matches:

SELECT b.ISBN, b.TITLE
FROM BOOKS b JOIN
     BOOK_SUBJECT bs 
     ON bs.ISBN = b.ISBN 
WHERE bs.SUBJECT_ID IN (47, 48)
GROUP BY b.ISBN, b.TITLE
ORDER BY COUNT(*) DESC;
READ ALSO
How to make uppercase only the odd indexes of a string in MySQL?

How to make uppercase only the odd indexes of a string in MySQL?

I'm trying to make only the odd indexes of a string in uppercase (whereas the even indexes to be in lowercase) in MySQL

106
mysql attendance record per entry card per date and time

mysql attendance record per entry card per date and time

I am new to MySQL and I am working on a stored procedure where I am getting month, year and empid as inputs and I need to get the output as below

70
How to display data date MYSQL /2 hours

How to display data date MYSQL /2 hours

i have table in DB like this

68