Clever Geek Handbook

MySQL Order by NULL join

I am trying to do an ORDER BY where I want any strings without an entry in the join table to appear at the bottom of the list and then ordered by name. Simplified tables:

users (id, name) photos (id, filename, user_id)

So far I have:

SELECT name FROM users 
LEFT OUTER JOIN photos ON photos.user_id = users.id
ORDER BY *ANSWER HERE*, name DESC

Many thanks.

+2


source to share


2 answers


You can use this:

ORDER BY ISNULL(photos.id), name DESC


The ISNULL () function will return 1 or 0, which will be convenient for you to sort in the order you want.

+10


source 


SELECT name FROM users 
LEFT OUTER JOIN photos ON photos.user_id = users.id
ORDER BY photos.user_id DESC, name DESC


ORDER BY photos.user_id DESC

will show NULL values ​​at the end.

0


source






More articles:


All Articles