Get all parents for a child using SQL

Question

I have a table with attributes (CommunityID, Name, Description, ParentCommunityID) called communities. CommunityID is the primary key and ParentCommunityID is a foreign key refer to this same table. Communities which doesn't have a parent has NULL in their ParentCommunityID value. I want to select all the parents of a Community.

I have tried using a answer found in here. but when I enter a community which don't have Parent Community (In my case parentCommunityID IS NULL) It gives wrong answer. Also If I remove DISTINCT keyword from select DISTINCT ParentCommunityID it gives same CommunityID twice sometimes.

with communities as 
(
   select CommunityID, ParentCommunityID
   from communities
   where CommunityID = 268 
   union all
   select c.CommunityID, c.ParentCommunityID
   from communities c
   join communities p on c.CommunityID = P.ParentCommunityID 
   AND c.CommunityID<>c.ParentCommunityID 
) 
select ParentCommunityID
from communities
ORDER BY ParentCommunityID;

I can't figure out what's wrong with this code? I want to select all the Parents of a Community. I don't have a clear idea about this Query as well.

Answer 1

You are using "communities" as an alias for with. It should be different so you could have a clear definition of your join:

with _communities as 
(
   select CommunityID, ParentCommunityID
   from communities
   where CommunityID = 11
   union all
   select p.CommunityID, p.ParentCommunityID
   from communities p
   join _communities c on p.CommunityID = c.ParentCommunityID 
) 
select ParentCommunityID
from _communities
where ParentCommunityId is not null
ORDER BY ParentCommunityID;

Here is DBFiddle demo.