Loading

how to get output of below query using join

how to get output of below query using join

Pramod Gupta

Pramod Gupta

7y855SQL

we have table table1

ID Name

121 A

121.1 B

122 C

121.2 D

121.3 E

122.1 F

122.2 G

how to get below output by using joins

ID id2

121 121.1

121 121.2

121 121.3

122 122.1

122 122.2

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar7y ago
You can try the below query using cross join in SQL server
create table #temp (id float, names char(1))
insert into #temp values(121, 'A'),
(121.1, 'B'),
(122, 'C'),
(121.2,'D'),
(121.3, 'E'),
(122.1, 'F'),
(122.2, 'G ')

select distinct a.id, b.id from (
select * from #temp where id in (121, 122)
)a
cross join (
select * from #temp where id not in (121, 122)
)b where a.id =  CAST(b.id AS INT)

drop table #temp
The output is as shown below
ID  ID2
121 121.1
121 121.2
121 121.3
122 122.1
122 122.2
You can find the live demo here - https://rextester.com/JSCPX64544
You can also find your desired output using below simple sql server query
select CAST(ID AS INT) as ID,ID as ID2 from #temp
where id not in (121, 122) order by ID
Request you to accept the answer if you got the solution helpful.
0
Nakul Chaudhari7y ago
we can get required result without using join

select CAST(ID AS INT) as ID,ID as ID2 from table1 order by ID

is there any specific reason you want to use join ?

using join

select CAST(t1.ID AS INT) as ID,t1.ID as ID2 from table1 t1 inner join table1 t2 on t1.ID=t2.ID order by t1.ID
0