CODEWITHSUNDEEP
sqlpostgresql
nara_l
nara_l

Reputation: 674

How to get user's name rather than id from same table in Postgresql subquery?

I have a users table: with fields like: firstname, last_name..., manager_id...,

manager_id is just the id of another user.

When I run the query:

   SELECT DISTINCT ON(users.id) user.id, CONCAT(users.first_name, ' ', users.last_name) AS \
                'employee', users.email, \
                (SELECT manager_id), \
                (SELECT users.first_name from users where users.id = manager_id) as Manager,\
                (SELECT DISTINCT to_char(auth_user.last_login, 'Mon dd, yyyy')) as Last_Login FROM users INNER JOIN auth_user ON (users.user_id = auth_user.id)

This query (SELECT manager_id) returns the manager's id, however the next line

(SELECT users.first_name from users where users.id = manager_id) as Manager

returns blank. I also tried joining the table on itself which only returns the name of 1 manager:

(SELECT e.first_name FROM employees_employee e INNER JOIN employees_employee s ON s.id = e.coach_id limit 1)

Upvotes: 1

Views: 1196

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

This is because no users are their own managers. Always qualify your column names!

SELECT DISTINCT ON (u.id) u.id,
       CONCAT(u.first_name, ' ', u.last_name) AS employee
       u.email,
       u.manager_id,
       (SELECT u2.first_name FROM users u2 where u2.id = u.manager_id) as Manager,
       to_char(au.last_login, 'Mon dd, yyyy') as Last_Login
FROM users u INNER JOIN
     auth_user au
     ON u.user_id = au.id
ORDER BY u.user_id;

Upvotes: 3

Related Questions