I have a table like this
CREATE TABLE temp_sectors
sectorID INT NOT NULL PRIMARY KEY,
sectorName NVARCHAR(300) NOT NULL,
headSectorID INT NULL FOREIGN KEY REFERENCES temp_sectors(sectorID)
Now, how could I get a list of sectors starting from the one who has no headSectorID with the respective data hierarchy?
For example, if I say something like this
SELECT s.sectorID, s.sectorName, s2.sectorID, s2.sectorName FROM temp_sectors s
INNER JOIN temp_sectors s2 ON s.sectorID = s2.headSectorID
I would get only the first and the second level of hierarchy, and I need it all...