Knex join on column belonging to same same table-CodePudding

Using Knex.js, I am attempting to query a table of users. The only columns that are important for this scenario are internal_id (key), name, and supervisor_id. Every user (except one) has a supervisor and their record stored the ID of that supervisor. When viewing an individual user record in my app, I want to query the users table getting info specific to that user as well as a join on the same users table so I can retrieve the supervisor's name. I believe part of the problem I face is that I am including a WHERE internal_id = current page user id, so I am always returning the current user's name as their own supervisor. The query output to SQL is as shown. The results of the query are below. I've setup the query this way as Knex would not allow any left join on the same table without using aliases.

select [dbo].[olms-users].*, [dbo].[user-groups].[name] as [user_group_texts], [dbo].[olms-users].[name] as [supervisor_name]
from [dbo].[olms-users]
left join [dbo].[user-groups] on [dbo].[olms-users].[user_groups] = [dbo].[user-groups].[internal_id]
left join [dbo].[olms-users] as [all_users] on [dbo].[olms-users].[supervisor_id] = [dbo].[olms-users].[internal_id]
where [dbo].[olms-users].[internal_id] = '12716149'

Aside from creating more tables that I would do subqueries on, can what I'm trying to do be done with a single table and probably left join in Knex? I can also try to work backwards from any proposed SQL answer. Any help or insight is appreciated.

CodePudding user response：

Step 1: To make your query clearer use short, relevant, table aliases e.g.

U for [dbo].[olms-users]
UG for [dbo].[user-groups]
S for [dbo].[olms-users] where it relates to the supervisor

Step 2: Check that you are referencing the correct table/alias - it turns out you are not:

In your supervisor join you are using both columns from U instead of one from U and one from S.
In your query you are referencing U for the supervisor name instead of S.

select
    U.*
    , UG.[name] as [user_group_texts]
    -- Following needs to reference S not U!
    , S.[name] as [supervisor_name]
from [dbo].[olms-users] U
left join [dbo].[user-groups] UG on UG.[internal_id] = U.[user_groups]
-- Following need to join S to U, not U to U!
left join [dbo].[olms-users] S on S.[internal_id] = U.[supervisor_id]
where U.[internal_id] = '12716149';

Note: 3 part column naming (e.g. [dbo].[olms-users].[internal_id]) is to be depreciated in SQL Server so best to stop using it.