[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

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
0
James Atkin
Asked:
James Atkin
  • 8
  • 5
  • 4
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
James AtkinSenior Principle Software EngineerAuthor Commented:
Excellent!!!
Thanks so much for all the help :-)
0
 
PortletPaulCommented:
no problem, sorry it took a while.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now