mysql: sort tables rows using another table's data

Question

I have two tables. One is books table. It has two columns. id|title. Another is student_books table. It has two columns student_id|book_id. When a student tries to read a book, I check the student_books table whether the student_id and the book_id matches or not. A student can buy multiple books so there may have multiple entries in student_books table. now I want to show the list of books based on popularity(that has maximum entries in student_books table) on a page. How should I write the SQL query?

Can you provide the query that you have tried to write to get the data you want? What is your intended output of the query? Just the book titles ordered by popularity? or the actual count? — Tah
– Tah, Commented Jan 4, 2017 at 4:02

Gurwinder Singh · Accepted Answer · 2017-01-04 04:02:24Z

1

You can join books table with student_books and order by count for each book

select b.*
from books b
left outer join student_books s
on b.id = s.book_id
group by b.id, b.title
order by count(s.student_id) desc;

answered Jan 4, 2017 at 4:02

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

Comments

shmosel · Accepted Answer · 2017-01-04 04:14:32Z

0

SELECT b.id, b.title
  FROM books b
  LEFT JOIN students s ON s.book_id = b.id
 GROUP BY b.id, b.title
 ORDER BY COUNT(s.book_id) DESC;

edited Jan 4, 2017 at 4:14

answered Jan 4, 2017 at 4:02

shmosel

51k7 gold badges81 silver badges148 bronze badges

5 Comments

Tim Biegeleisen Over a year ago

You are selecting title but this column is not aggregate and also does not appear in the GROUP BY.

shmosel Over a year ago

@TimBiegeleisen Not a problem in MySQL.

Tim Biegeleisen Over a year ago

Yes, it is a problem in MySQL, and your query would not run in certain versions.

shmosel Over a year ago

@TimBiegeleisen Do you have a source or example?

Tim Biegeleisen Over a year ago

dev.mysql.com/doc/refman/5.7/en/… ... As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY.

Collectives™ on Stack Overflow

mysql: sort tables rows using another table's data

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related