1

I have a single table, Issue. This table has Id and ChildId columns.

=============
Id | ChildId
1  | NULL
2  | 3
3  | NULL
4  | 1
5  | NULL
6  | NULL
=============

I need to select from this table so that the parent row is returned if it exists else return the child row. So selecting from every row would get:

=============
Id | ChildId
4  | 1
2  | 3
2  | 3
4  | 1
5  | NULL
6  | NULL
=============

I know that this table layout is not good (using ChildId instead of ParentId) but I cannot change the scheme unfortunately.

If you know how to return this data that would be great. Thanks in advance.

  • 1
    Hi, Welcome, Could you tag your question with the correct version too? – Tom V Dec 14 '18 at 12:03
2

Join the same table twice and use a CASE statement:

select
    case when t2.id is not null then t2.id else t1.id end as id,
    case when t2.id is not null then t2.childid else t1.childid end as childid
from 
    tbl t1
left join tbl t2
    on t2.childid = t1.id
id | childid
-: | ------:
 4 |       1
 2 |       3
 2 |       3
 4 |       1
 5 |    null
 6 |    null

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.