question

ElayaRaja-3410 avatar image
0 Votes"
ElayaRaja-3410 asked JingyangLi answered

Need single query for this condition

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.

sql-server-transact-sqlazure-database-mysql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnuragSharma-08 avatar image
0 Votes"
AnuragSharma-08 answered

Hi @ElayaRaja-3410, 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

203192-image.png

Change the id for different values.


image.png (4.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
1 Vote"
Viorel-1 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JingyangLi avatar image
0 Votes"
JingyangLi answered
 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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.