Recursive query in SQL View

Hi all!

I have a database that has a number of tables in it.
I wish to create a view from three of them that requires recursion.
Can anyone please help me as this looks like it could be quite complex!!!
This image shows (hopefully!) what I am trying to achieve.

recursive.png
I aim aiming to get a set of 'AlarmStatus' results for each Group.

I could do this with recursive SQL calls, but I am sure a View is the correct way to do this?

Any help would be appreciated.

Many thanks,

James
LVL 4
James AtkinSenior Principle Software EngineerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
This result:
| BaseMapID | LinkMapID |
|-----------|-----------|
|         1 |         2 |
|         1 |         3 |
|         1 |         3 |
|         3 |         4 |

Open in new window


Produced by this "Recursive CTE" query:
;WITH CTE (BaseMapID, LinkMapID)
AS (SELECT
            BaseMapID
          , LinkMapID
      FROM tblLinks L
      UNION ALL
            SELECT
                  L.BaseMapID
                , L.LinkMapID
            FROM tblLinks L
            JOIN CTE
                  ON L.LinkMapID = CTE.BaseMapID
      )
SELECT
      *
FROM CTE
ORDER BY 
      BaseMapID
    , LinkMapID
;

Open in new window


details:
    CREATE TABLE tblMaps
        ([Name] varchar(8), [MapID] int)
    ;
        
    INSERT INTO tblMaps
        ([Name], [MapID])
    VALUES
        ('Main Map', 1),
        ('Sub1', 2),
        ('Sub2', 3),
        ('Sub3', 4)
    ;
    
    
    CREATE TABLE tblLinks
        ([BaseMapID] int, [LinkMapID] int)
    ;
        
    INSERT INTO tblLinks
        ([BaseMapID], [LinkMapID])
    VALUES
        (1, 2),
        (1, 3),
        (3, 4)
    ;
    
**Query 1**:

    ;WITH CTE (BaseMapID, LinkMapID)
    AS (SELECT
                BaseMapID
              , LinkMapID
          FROM tblLinks L
          UNION ALL
                SELECT
                      L.BaseMapID
                    , L.LinkMapID
                FROM tblLinks L
                JOIN CTE
                      ON L.LinkMapID = CTE.BaseMapID
          )
    SELECT
          *
    FROM CTE
    ORDER BY 
          BaseMapID
        , LinkMapID
    

**[Results][2]**:
    | BaseMapID | LinkMapID |
    |-----------|-----------|
    |         1 |         2 |
    |         1 |         3 |
    |         1 |         3 |
    |         3 |         4 |

  [1]: http://sqlfiddle.com/#!6/d47ef/6

Open in new window

0
 
PaulCommented:
Please supply DDL for the tables and some sample data.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can do that with an UNION ALL for the two requirements:
SELECT g.ID, g.GroupName, l.EntryID, l.EntryType
FROM tblGroups g
	INNER JOIN tblLinks l ON g.ID = l.GroupID
	INNER JOIN tblEquipment E ON l.EntryID = l.ID
WHERE l.EntryType = 0
UNION ALL
SELECT g.ID, g.GroupName, l.EntryID, l.EntryType
FROM tblGroups g
	INNER JOIN tblLinks l 
		INNER JOIN tblGroups g2 ON l.EntryID = g2.ID
	ON g.ID = l.GroupID
WHERE l.EntryType <> 0

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
PaulCommented:
I really would like to see how the data is actually stored by looking at sample data
(in particular can [GroupID] be NULL?)

But perhaps this will work:
select
      L.*, G.ID as GID, G.GroupName, E.ID as EID, E.AlarmStatus
from tblLinks L
left outer join tblGroups    G on L.GroupID = G.ID and L.EntryType = 1
left outer join tblEquipment E on L.EntryID = E.ID and L.EntryType = 0

Open in new window


details
**MS SQL Server 2008 Schema Setup**:

    CREATE TABLE tblGroups
    	([ID] int, [GroupName] varchar(15))
    ;
    	
    INSERT INTO tblGroups
    	([ID], [GroupName])
    VALUES
    	(1, 'xxxxxxxxxxxxxxx')
    ;
    
    CREATE TABLE tblEquipment
    	([ID] int, [AlarmStatus] int)
    ;
    	
    INSERT INTO tblEquipment
    	([ID], [AlarmStatus])
    VALUES
    	(1, 1),
    	(2, 0)
    ;
    
    CREATE TABLE tblLinks
    	([GroupID] int, [EntryID] int, [EntryType] int)
    ;
    	
    INSERT INTO tblLinks
    	([GroupID], [EntryID], [EntryType])
    VALUES
    	(1, NULL, 1),
    	(1, 1, 0),
    	(1, 2, 0)
    ;

**Query 1**:

    select
          L.*, G.ID as GID, G.GroupName, E.ID as EID, E.AlarmStatus
    from tblLinks L
    left outer join tblGroups G on L.GroupID = G.ID
    left outer join tblEquipment E on L.EntryID = E.ID
    
    

**[Results][2]**:
    
    | GROUPID | ENTRYID | ENTRYTYPE | GID |       GROUPNAME |    EID | ALARMSTATUS |
    |---------|---------|-----------|-----|-----------------|--------|-------------|
    |       1 |  (null) |         1 |   1 | xxxxxxxxxxxxxxx | (null) |      (null) |
    |       1 |       1 |         0 |   1 | xxxxxxxxxxxxxxx |      1 |           1 |
    |       1 |       2 |         0 |   1 | xxxxxxxxxxxxxxx |      2 |           0 |



  [1]: http://sqlfiddle.com/#!3/f5fdc/3

Open in new window

0
 
James AtkinSenior Principle Software EngineerAuthor Commented:
Hi,

I actually have two different situations where this sort of requirement is needed so I changed the table names & field names to generic ones to match the question.

I'll get the DDL for one such example in a few minutes...
0
 
James AtkinSenior Principle Software EngineerAuthor Commented:
Here, we have 6 Equipment and 2 Links
The top map has equipment 6,4 & 2 plus a link to map 2 (Middle Map)
The Middle Map has equipment 5 & 3 plus a link to map 3 (Bottom Map)
The Bottom map just has equipment 1

tblMaps
ID   MapName       MapImage   ImageType
1    "Top Map"     <data>     0
2    "Middle Map"  <data>     0
3    "Bottom Map"  <data>     0

tblEquipmentOnMap
ID   MapID   EquipmentID   EntryType   CoordinateX   CoordinateY 
1    1       6             0           0.1           0.1
2    2       5             0           0.2           0.2
3    1       4             0           0.3           0.3
4    2       3             0           0.4           0.4
5    1       2             0           0.5           0.5
6    3       1             0           0.6           0.6
7    1       2             1           0.7           0.7
8    2       3             1           0.8           0.8

tblEquipment
ID   AlarmStatus
1    2
2    2
3    3
4    3
5    4
6    4

Open in new window


If I query Map 3, I should see only Equipment 1
If I query Map 2, I should see Equipment 1, 3 & 5
If I query Map 1, I should see Equipment 1, 2, 3, 4, 5 and 6
If the results include the links too, that is not a problem as these can be filtered by EntryType.

Hopefully this all makes some sense, but please let me know if anything is not clear.

Thanks,

James
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
This requirement is new. Doesn't have nothing with the original one.
Even the words "Recursive" and "View" in title doesn't seem to fits in this question.
0
 
James AtkinSenior Principle Software EngineerAuthor Commented:
Ummm... maybe I have not explained the explanation well enough :-)

The View that I wish to create would need to be recursive because, for example, for Map 1:
Map 1 = Equipment 1, 3 & 5 + Map 2
Map 2 = Equipment 2 & 4  + Map 3
Map 3 = Equipment 6
Therefore I should get Map 1 = 1, 2, 3, 4, 5 & 6

The entire contents of the resulting view should be
MapID   EquipmentID   AlarmStatus
1       6             4
1       5             4
1       4             3
1       3             3
1       2             2
1       1             2
2       5             4
2       3             3
2       1             2
3       1             2

Open in new window

Does this make sense?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, maybe it's my problem since I couldn't and still can't understand what this new tables has to do with the first ones you posted here.
Also, the solution is kind of different for this last requirement compared with the first one.
As my understanding this is a new question.
0
 
James AtkinSenior Principle Software EngineerAuthor Commented:
I have two different tasks needing the same solution, I thought the Map illustration was easier for others to visualise.

For the above, the equivalent tables / fields in the original question are as follows:
tblGroups = tblMaps
 - GroupName = MapName

tblLinks = tblEquipmentOnMap
 - GroupID = MapID
 - EntryID = EquipmentID
 - EntryType = EntryType

tblEquipment = tblEquipment
 - ID = ID
 - AlarmStatus = AlarmStatus

I am happy to post this as a different question if you would prefer it?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No worries. I'll try to go with this one.
For sure I misunderstood your question.
0
 
James AtkinSenior Principle Software EngineerAuthor Commented:
Hi,

Did you have any thoughts on how I may be able to do this?

Thanks a lot,

James
0
 
PaulCommented:
So, where in the data do you locate the fact that MAP 1 is parent of MAP 2 and that is parent of MAP 3?

It does not exist

e.g. tblMaps needs an additional column [ParentMap]
                                                                                               
tblMaps
ID   MapName       ParentMap MapImage   ImageType
1    "Top Map"      NULL     <data>     0
2    "Middle Map"   1        <data>     0
3    "Bottom Map"   2        <data>     0

Open in new window

0
 
James AtkinSenior Principle Software EngineerAuthor Commented:
Hi,

The tblEquipmentOnMap contains links to both equipment and sub-maps.
When the EntryType is set to '1' the value of 'EquipmentID' relates to the Map ID

I can break the tblEquipmentOnMap into two if it makes the problem easier to solve, having one with ONLY equipment links, and one with ONLY Map Links.

Unfortunately I cannot add a parentID in the Map table as this is not a fixed hierarchy. A Map link may be present on one or more maps.
0
 
James AtkinSenior Principle Software EngineerAuthor Commented:
What about if we simply the requirement back to the core.

Say we have two tables, one for Map Info (tblMaps) and one detailing the relationships (tblLinks).

tblMaps has 'Name' and 'MapID'
tblLinks has 'BaseMapID' (which is the map that contains the link) and 'LinkMapID' (which is the ID of the map it links to)

tblMaps
Name        MapID
"Main Map"  1
"Sub1"      2
"Sub2"      3
"Sub3"      4

Open in new window

tblLinks
BaseMapID   LinkMapID
1           2
1           3
3           4

Open in new window

Would it be possible to create a View to produce these results:
BaseMapID   MapID
1           2
1           3
1           4
3           4

Open in new window

I believe this is quite straightforward in Oracle using the CONNECT BY PRIOR commands, but these are sadly Oracle only!

If I can get this far, I can plumb in the remainder.

Can anyone help with this please?

Thanks,

James
0
 
James AtkinSenior Principle Software EngineerAuthor Commented:
Excellent!!!
Thanks so much for all the help :-)
0
 
PaulCommented:
no problem, sorry it took a while.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.