Marcilio Leite Marcilio Leite - 1 month ago 6
SQL Question

Order by one of two aliases (for relationships of the same table)

I have the tables users and projects.

Projects has:

project_manager_id -> references users.id
business_manager_id -> references users.id


I have to order by one of those. So, my query is:

select * from projects...
LEFT JOIN profiles project_managers ON project_managers.id = projects.project_manager_id
LEFT JOIN profiles business_managers ON business_managers.id = projects.project_manager_id
order by project_managers.name


OR

order by business_managers.name


But for both order by, the result is the same. My result is ordered by the first profiles.name JOIN (project_managers.name).

How can I order it correctly, by project_managers.name or business_manager.name?

Answer

Something like this would work

select * from (
    select projects.id project_id 
    , project_managers.name project_managers_name
    , business_managers.name business_managers_name
    , ...
    from projects
    LEFT JOIN profiles project_managers 
    ON project_managers.id = projects.project_manager_id
    LEFT JOIN profiles business_managers 
    ON business_managers.id = projects.project_manager_id
) projects
order by project_managers_name 
Comments