Mysql join with sorting

Question

I have two tables say: user and library. The library contains books sorted in a certain way; a user can elect to sort his books in a certain way as well. The structure (and sample data) for the two tables will look thus:

library

bookid  position
10         1
12         2
14         3
16         4

user

userid  bookid  position
12669      12         1
12669      10         2

I want a query to return all the books for user, 12669, sorted by position i.e:

select bookid from user where userid = 12669 group by position

After it has return these sorted books, it should return the other bookids (not present in user) that are in the library. No bookid should be repeated. The result of these scenario will look thus:

12
10
14
16

In other words: All the books in library should be returned by this query but with the books selected by user sorted according to user.position

I reckon I may need some kind of join statement for this. I tried:
select bookid from user u right join library l on u.bookid = l.bookid where u.userid = 12669 group by u.position

However, I get a syntax error for this. What is the best way to solve this 'problem'? Many thanks.

Answer 1

First of all, your posted query includes in its projection a column name which doesn't belong to either of the tables involved.

Secondly, you are using GROUP BY where sorting is carried out by ORDER BY.

Third point: As @Romil points out, the reference to the USER table in the WHERE clause overrides the outer join, and effectively enforces an inner join. So you need to select from an inline view on the USER table.

Finally, to get the sort order you want, you need to band all the USER.POSITIONs first. This version of your query uses IFNULL to assign an extremely high value to any rows which don't have a joined USER record. So it will sort by all the returned USER.POSITION values then by LIBRARY.POSITION for all the remaining books.

select l.bookid 
from ( select * from user
        where userid = 12669 ) u
   right join library l 
     on (u.bookid = l.bookid) 
order by ifnull(u.position, 999999999) , l.position

NB: if you are indexing the Library of Babel and so might have enough shelves to support more than 999999999 positions just bump up that subsituted value.

---

"This didn't work for me. It didn't return the bookids in the arrangement I wanted. "

Frankly I find that surprising. Here is a SqlFiddle which definitely returns your sample data in the order you specify. So I repeat my earlier question: do your posted table descriptions match your actual tables exactly ?

Answer 2

This should do the trick :)

SELECT l.bookid
FROM library l
LEFT OUTER JOIN user u on u.bookid = l.bookid
WHERE u.userid = 12669
ORDER BY isnull(u.position, 99999), l.position