CODEWITHSUNDEEP
sqlsql-order-by
Matteo Bononi 'peorthyr'
Matteo Bononi 'peorthyr'

Reputation: 2220

Order sql results using a Count() value from another table

assuming we have a users table like this:

ID  |  USERNAME  |  STATUS  |
01  |  Aname     |    1     |
02  |  Bname     |    1     |
03  |  Cname     |    2     |
04  |  Dname     |    1     |

there are also two other tables, where, for each user, records the items that the users "has" for istance, video and photos, lets assume the video table is like this:

ID  |  USER      |  VIDEO   |
01  |  01        |   aaaa   |
02  |  01        |   bbbb   |
03  |  02        |   cccc   |
04  |  02        |   dddd   |
05  |  03        |   eeee   |
06  |  03        |   ffff   |
07  |  03        |   gggg   |

the same for the photos:

ID  |  USER      |  PHOTO   |
01  |  01        |   aaaa   |
02  |  02        |   cccc   |
03  |  02        |   dddd   |
04  |  03        |   eeee   |
05  |  03        |   ffff   |
06  |  03        |   gggg   |

now, i'm trying to order the results of the first table, on the STATUS field, so the users with status 2 will go at the end (or beginning) of the results, but i would like to order the others by the fact that they have, or have not videos or photos. Actually for all the users in the status 1 I check if they have video AND photos and display a message like "warning, you have no videos" or something, i would like also to order all the users on the status basis AND on the fact that they have or not photos or video... something like:

SELECT id,username,status,NumOfPhotos,NumOfVideos order by status ASC, numOfPhotos ASC, NumOfVideos DESC

where NumOfPhotos, for example is SELECT COUNT(*) FROM PHOTOS WHERE USER = USERS.ID

Is that possible? is that possible in a single statement?

Upvotes: 0

Views: 323

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

This is an example on SQL Server, but something similar should work well on other RDBMS

SELECT 
id, username, status, COALESCE(NumOfPhotos,0) AS NumOfPhotos,COALESCE(NumOfVideos,0) AS NumOfVideos 
FROM dbo.Users u 
LEFT JOIN (SELECT [USER], COUNT(*) AS NumOfPhotos FROM dbo.Photos GROUP BY [USER]) ph ON ph.[USER] = u.ID
LEFT JOIN (SELECT [USER], COUNT(*) AS NumOfVideos FROM dbo.Videos GROUP BY [USER]) vd ON vd.[USER] = u.ID
ORDER BY status ASC, numOfPhotos ASC, NumOfVideos DESC

SQLFiddle

Upvotes: 2

Related Questions