CODEWITHSUNDEEP
mysqljoin
Cudos
Cudos

Reputation: 5894

MySQL JOIN based on sortorder field

I have a database with several users that each can have several books.

I want to get a list of users and their first book based on the (Integer) sortorder field in the table book.

Below is a example of the SQL I use now. Only problem is that it does not return the first book based on the sortorder field for each user.

SELECT
    b.id, b.user_id, b.sortorder, b.title
FROM
    books AS b
JOIN
    books_categories AS bc
ON
    (b.id =  bc.book_id)
JOIN
    categories AS c
ON
    (c.id =  bc.category_id)
JOIN
    users AS u
ON
    (u.id =  b.user_id)
GROUP BY
    b.user_id

Upvotes: 0

Views: 58

Answers (1)

a'r
a'r

Reputation: 37029

You need to join to a subquery that selects the first book for each user.

SELECT b.id, b.user_id, b.sortorder, b.title
FROM books b
JOIN (
    SELECT b2.user_id, min(b2.sortorder) sortorder
    FROM books b2
    GROUP BY b2.user_id
) first_books USING (user_id, sortorder);

Upvotes: 2

Related Questions