Need single query for this condition

Question

Id C1 C2

---

1 A A1

---

2 B B1

---

3 A A2

---

4 A A

---

5 B B3

---

6 B B4

---

If I give the ID value, I have to show the equaling C2 value
condition 1. if C2= C1 then I have to get all C2 values where C2=C1
eg. id= 4, out put is A, A1, A2
id = 5 , output is B3
id= 1, out put is A1
Thanks in Advance.

Answer 1

Hi @Elaya Raja , welcome to Microsoft Q&A forum.

Please try below:

select CASE WHEN b.C2 is null THEN a.c2 else b.c2 end as output from query a  
left join query b on a.C2 = b.C1  
where a.id = 4  

Change the id for different values.

Answer 2

Try a solution:

declare @id int = 4

select t1.C2
from MyTable t1
inner join MyTable t2 on t2.Id = t1.Id or (t2.C1 = t2.C2 and t2.C1 = t1.C1)
where t2.Id = @Id

Answer 3

declare @id int=4
create table test   (Id int, C1 varchar(2), C2 varchar(2))
insert into test  values 
 (1,'A','A1')
,(2,'B','B1')
,(3,'A','A2')
,(4,'A','A')
,(5,'B','B3')
,(6,'B','A4')
 select * from test t1
 where t1.id=@id or 
 exists(select 1 from test t2 where  t2.id=@id  and t1.C1=t2.C2)


 drop table test