CODEWITHSUNDEEP
sqlkata
FLash
FLash

Reputation: 746

sqlkata - Self Join; Add aliases while joining

What is the right way to do a self join using SQL KATA? Currently, I am only able to do the following

var query1 = new Query("Users");
var query = new Query("Users")
    .Join(query1.As("a"), j => j.On("a.Name", "Users.ParentName"))
    .Where("Name", "Sami");

But this generates the following query

SELECT
*
FROM
[Users]
INNER JOIN (
    SELECT
    *
    FROM
    [Users]
) AS [a] ON ([a].[Name] = [Users].[ParentName])
WHERE
[Name] = 'Sami'

Is it possible to achieve the following query?

SELECT
*
FROM
[Users] [a]
INNER JOIN [Users] [b] ON ([a].[ParentName] = [b].[Name])
WHERE
[a].[Name] = 'Sami'

Upvotes: 1

Views: 3106

Answers (1)

amd
amd

Reputation: 21462

No need to create a separate query instance here, do it like any other table. the key here is to use the table as alias syntax to instruct SqlKata that you are using a different alias.

var query = new Query("Users as a")
    .Join("Users as b", "a.ParentName", "b.Name")
    .Where("a.Name", "sami");

this will generate the following SQL:

SELECT
  *
FROM
  [Users] AS [a]
  INNER JOIN [Users] AS [b] ON [a].[ParentName] = [b].[Name]
WHERE
  [a].[Name] = 'sami'

check the following example directly on Sqlkata playground Self Join Example on SqlKata Playground

Upvotes: 4

Related Questions