child id and parent id mapping in same sql table

Question

I am trying to get the column named listCode by writing below query but it is coming as empty. In this query I am trying to map parentid to listid in same table and get only those parent records in listcode column

SELECT ListCode FROM mytable WHERE ListType = 'CNT' AND ListId =
(SELECT ParentId  FROM mytable WHERE ListType = 'CNT')

Answer 1

You can do it with IN:

SELECT ListCode FROM mytable
WHERE
  WHERE ListType = 'CNT'
  AND 
  ListId IN (SELECT Parentid FROM mytable)

or with EXISTS:

SELECT m.ListCode FROM mytable m
WHERE
  WHERE m.ListType = 'CNT'
  AND EXISTS (
    SELECT 1 FROM mytable
    WHERE Parentid = m.ListId
)