Saturday, March 3, 2018

Self join SQL Interview Question

This seems like a good interview question. Let's suppose in our database, there is a Section table with a SectionId and a ParentSectionId. ParentSectionId contains the SectionId of the parent.
Now, find sections and which are parents of sections other than themselves.

SELECT S.SECTIONID FROM Section S  INNER JOIN
Section S2 ON S.SectionId= S2.ParentSectionId
WHERE S2.ParentSectionId <> S2.SectionId)


Alternatively,

SELECT * FROM Section S  AND
(SELECT COUNT(*) FROM Section S2
WHERE S2.ParentSectionId = S.SectionId
AND S2.ParentSectionId <> S2.SectionId)

No comments:

Post a Comment