sql select query self join or loop through to fetch records

Question

I have this kind of scenario in sql server I have table named Room and here is the data of it and I want output something like this as shown in this picture I have tried to show my table named room and then on top of it I have placed tag input which have RoomId,ConnectingRoomID and many more other columns now what I want is a sql select query that can return me the scenario I have placed with tag name output..

These values are self created I have thousand of rooms and in room table and thousand of connecting room with it hope my question is clear enough thanks.

Answer 1

I think you can use this:

with x as (
    select *, sum(case connectingroomid when 0 then 1 else 0 end) over(order by roomid) as grp
    from rooms
)
select x.roomid, (select min(x2.roomid) as min_roomid from x x2 where x2.grp = x.grp) as connectingroomid
from x

Answer 2

This is a recursive query: For all rooms go to the connecting room till you find the one that has no more connecting room (i.e. connecting room id is 0).

with rooms (roomid, connectingroomid) as 
(
  select 
    roomid,
    case when connectingroomid = 0 then 
      roomid 
    else 
      connectingroomid 
    end as connectingroomid
  from room
  where connectingroomid = 0
  union all
  select room.roomid, rooms.connectingroomid 
  from room
  inner join rooms on room.connectingroomid = rooms.roomid
) 
select * from rooms
order by connectingroomid, roomid;

Here is the SQL fiddle: http://www.sqlfiddle.com/#!3/46ed0/1.

EDIT: Here is the explanation. Rather than doing this in the comments I am doing it here for better readability.

The WITH clause is used to create a recursion here. You see I named it rooms and inside rooms I select from rooms itself. Here is how to read it: Start with the part before UNION ALL. Then recursively do the part after UNION ALL. So, before UNION ALL I only select the records where connectingroomid is zero. In your example you show every room with its connectingroomid except for those with connectingroomid for which you show the room with itself. I use CASE here to do the same. But now that I am explaining this, I notice that connectingroomid is always zero because of the WHERE clause. So the statement can be simplified thus:

with rooms (roomid, connectingroomid) as 
(
  select 
    roomid,
    roomid as connectingroomid
  from room where connectingroomid = 0
  union all
  select room.roomid, rooms.connectingroomid 
  from room
  inner join rooms on room.connectingroomid = rooms.roomid
) 
select * from rooms
order by connectingroomid, roomid;

The SQL fiddle: http://www.sqlfiddle.com/#!3/46ed0/2.

With the part before the UNION ALL I found the two rooms without connecting room. Now the part after UNION ALL is executed for the two rooms found. It selects the rooms which connecting room was just found. And then it selects the rooms which connecting room was just found. And so on till the join returns no more rooms.

Hope this helps understanding the query. You can look for "recursive cte" on the Internet to find more examples and explanations on the topic.

Answer 3

select RoomID,
  (Case when RoomID<=157 then 154
   else 158 end) ConnectingRoomID
from Input

Answer 4

First of all, your output is not correct: Room 154 should also connect to room 0 :-)

What you are after is the transitive closure of the relation defined by the table Room. It is impossible to get this with "vanilla" SQL. There are however, a few extensions to SQL to make recursive queries possible.

For example, If I have a relation "graph":

src | target 
-----+--------
  1 |      2
  2 |      3
  3 |      4
  5 |      6
  6 |      7

I can define a new table "closure" with the same fields:

WITH RECURSIVE closure (src, target) AS 
 (SELECT src, target FROM 
   graph 
     UNION
   SELECT graph.src, closure.target FROM graph, closure WHERE 
     graph.target = closure.src) 
 SELECT * FROM closure

Note that "closure" occurs in its own definition (that is why this is a recursive query) It uses the original graph as a "seed" and grows by adding tuples with increasing distance (inspecting itself to do so).

The result (it clearly shows how the relation has grown):

 src | target 
-----+--------
   1 |      2
   2 |      3
   3 |      4
   5 |      6
   6 |      7
   1 |      3
   2 |      4
   5 |      7
   1 |      4

If you are only interested in pairs that cannot be extended further, as in your original example, you could add an extra field "distance" to the closure table and use a GROUP BY clause to keep only the maximal pairs.

Disclaimer: I'm not on Windows, and used postgres for this. MS SQL should work very much the same way.

Answer 5

try below sql:

Assumming @input is your input table
Note: I added an ID column in the @input table

declare @input table
(
 id int identity,
 RoomId int,
 ConnectingRoomId int
)

insert into @input
select 154,0 union all
select 155,154 union all
select 156,155 union all
select 157,156 union all
select 158, 0 union all
select 159, 158 union all
select 160, 159

**UPDATED: remove the union **
SQL:

select
 d.id,  
 d.roomId
 ,max(d.connectingRoomId) as ConnectingRoomId
 from
(
select 
    bb.id,
    bb.RoomId 
    ,b.RoomId as connectingRoomId
from @input b
right join
(
 select 
    a.id,
    a.RoomId,a.ConnectingRoomId
 from @input a

) bb on (b.id < bb.Id) or b.Id = bb.Id
where b.ConnectingRoomId = 0
) d
group by d.id, d.RoomId

/*
Result (OUTPUT TABLE)

id          roomId      ConnectingRoomId
----------- ----------- ----------------
1           154         154
2           155         154
3           156         154
4           157         154
5           158         158
6           159         158
7           160         158
*/