Solved

Recursive query in SQL View

Posted on 2015-02-19
17
66 Views
Last Modified: 2015-03-10
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
Comment
Question by:jatkin
  • 8
  • 5
  • 4
17 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40618603
Please supply DDL for the tables and some sample data.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40618701
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40620251
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
 
LVL 4

Author Comment

by:jatkin
ID: 40620664
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
 
LVL 4

Author Comment

by:jatkin
ID: 40620677
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40620935
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
 
LVL 4

Author Comment

by:jatkin
ID: 40620972
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40620978
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Author Comment

by:jatkin
ID: 40620988
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40621018
No worries. I'll try to go with this one.
For sure I misunderstood your question.
0
 
LVL 4

Author Comment

by:jatkin
ID: 40644000
Hi,

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

Thanks a lot,

James
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40644119
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
 
LVL 4

Author Comment

by:jatkin
ID: 40644128
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
 
LVL 4

Author Comment

by:jatkin
ID: 40646546
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40655282
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
 
LVL 4

Author Closing Comment

by:jatkin
ID: 40655749
Excellent!!!
Thanks so much for all the help :-)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40655751
no problem, sorry it took a while.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now