CODEWITHSUNDEEP
sqlsql-serverinner-joinself-join
Vixen440
Vixen440

Reputation: 3

Inner join followed by a self join

I have two tables PERSON and PERSONRELATION.

On the PERSONtable I have the columns PERSONpk, FIRSTNAME, and LASTNAME.

On the PERSONRELATION table I have MANAGER1fk which is the a fk for PERSONpk.

I am trying to join the two tables, bring over all the fields above and self join back to add the MANAGERFIRSTNAME and MANAGERLASTNAME. Here is what I have done but it apparently isn't correct:

Select PERSONpk, MANAGER1FK,e.FIRSTNAME,e.LastName,m.FIRSTNAME,M.LASTNAME
FROM dbo.PERSON e
INNER JOIN dbo.PERSONRELATION rel
ON rel.PERSONFK = PERSONPK
INNER JOIN dbo.PERSON m ON e.PERSONpk = m.MANAGER1FK

Upvotes: 0

Views: 1449

Answers (1)

xQbert
xQbert

Reputation: 35323

Three issues:

  • Your last join was wrong (you were joining back to e when I believe you wnat to join rel.Manager1FK to m. Think of it linearly... I have an employee... an employee can have many relations... each relation is to another employee... and based on the column names, these relations are employee manager relations. So you go from P-->REL-->M M--P can't exist w/o REL in your data example.
  • You should alias e.personpk on the 2nd join
  • given you have to reference person twice all columns from person must be aliased (even in the select)

Table personrelation contains the FK for both the employee and the manager to person; thus you need to join the 2nd fk field from personrelation (MANAGER1fk) (and this isn't a self join) If the hierarchy were all maintained in person it would be a self join.

SELECT e.PERSONpk
     , rel.MANAGER1FK
     , e.FIRSTNAME
     , e.LastName
     , m.FIRSTNAME
     , M.LASTNAME
FROM dbo.PERSON e
INNER JOIN dbo.PERSONRELATION rel
  ON rel.PERSONFK = e.PERSONPK
INNER JOIN dbo.PERSON m 
  ON m.PERSONpk = rel.MANAGER1FK

Upvotes: 1

Related Questions