CODEWITHSUNDEEP
mysqlsqlsearch
mother loadmining
mother loadmining

Reputation: 23

Ordering by the number of matches

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

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 1

Related Questions