?
Solved

Complicated SQL Query (perhaps recursive?) - SQLCE3.5

Posted on 2009-02-14
1
Medium Priority
?
985 Views
Last Modified: 2012-08-13
Please note that this is for SQL CE - which appears to have a limited subset of functionality, e.g., no IF or DECLARE!  I would usually do this using IF and DECLARE, LOOPs etc as I am not very good at making complex SQL ststements, so I am hoping that someone can help with this!

I have also included example tables and data to help with generating the answer....

I have two SQL tables as follows:

create table netObject
                    noPK int identity(1,1) primary key,
                    noName nvarchar(100) not null unique,
                    noType nvarchar(100) not null

create table netGroupObject
                    ngmPK int identity(1,1),
                    ngmPKParent int not null,
                    ngmPKChild int not null

ngmPKParent and ngmPKChild are values that must exist in netObject.noPK

The logic is that for each ngmPK the name of the group is defined by ngmPKParent and the children are ngmPKChild.  Groups can be nested, so the first child could also be a parent for more children.

NB: The logic around the insertion process prevents a circular relationship from occuring, so I do not have to worry about testing for this.

What I want is all the ROWS in netObject that can be derived from netObject.noName

Now I can do this using iteration and many statements but I am looking for a better way.

In the code section is what I am using to generate test data for the following group structures.  I have included this to make it easy for someone to write something that works!

Group1
  value1, value2

Group2
  value1, value3

Group3
  value2, value4

Group4
  Group1, Group3

Group5
  Group1, Group2, value6, value7

Group6
  value9, Group5

So the following bit of SQL gets all the entries in Group1:

select noPK, noName, noType from netObject where noPK in (select ngmPKChild from netGroupObject where ngmPKParent in (select noPK from netObject where noName in ('Group1')))

noPK      noName      noType
1              value1     value
2              value2     value

Now that is easy where I know for certain that the group does not contain any subgroups, such as is the case for Group4 for which I could programatically build up the following statement:

select noPK, noName, noType from netObject where noPK in (select ngmPKChild from netGroupObject where ngmPKParent in (select ngmPKChild from netGroupObject where ngmPKParent in (select noPK from netObject where noName in ('Group4'))))

To result in:
noPK      noName      noType
1              value1     value
2              value2     value
4              value4     value

To Get the results for Group6, I have to build up a resultset from multiple result sets, I can not find an SQL statement to automatically return only the bottom most records.

Of course, if the SQL Statement gets 'Value8' it should also be able to just return that record as well, as it has NO entries in netGroupObject so will not be a group.

So to ReCap, I am looking for an SQL Statement that will take a value of netObject.noName and show all the related records in netObject that do not have a entry in netObjectGroup.ngmPKParent.

Thanks.

-Rowan
create table netObject (
    noPK int identity(1,1) primary key,
    noName nvarchar(100) not null unique,
    noType nvarchar(100) not null,
    noComment nvarchar(1000) not null
)
go
create table netGroupObject (
    ngmPK int identity(1,1),
    ngmPKParent int not null,
    ngmPKChild int not null
)
go
delete from netGroupObject
go
delete from netObject
go
insert netObject(noName,noType,noComment) values('value1','value','');
insert netObject(noName,noType,noComment) values('value2','value','');
insert netObject(noName,noType,noComment) values('value3','value','');
insert netObject(noName,noType,noComment) values('value4','value','');
insert netObject(noName,noType,noComment) values('value5','value','');
insert netObject(noName,noType,noComment) values('value6','value','');
insert netObject(noName,noType,noComment) values('value7','value','');
insert netObject(noName,noType,noComment) values('value8','value','');
insert netObject(noName,noType,noComment) values('value9','value','');
insert netObject(noName,noType,noComment) values('Group1','group','');
insert netObject(noName,noType,noComment) values('Group2','group','');
insert netObject(noName,noType,noComment) values('Group3','group','');
insert netObject(noName,noType,noComment) values('Group4','group','');
insert netObject(noName,noType,noComment) values('Group5','group','');
insert netObject(noName,noType,noComment) values('Group6','group','');
insert netObject(noName,noType,noComment) values('Group7','group','');
insert netObject(noName,noType,noComment) values('Group8','group','');
insert netObject(noName,noType,noComment) values('Group9','group','');
 
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group1' then noPK else 0 end),
     max(case when noName = 'value1' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group1' then noPK else 0 end),
     max(case when noName = 'value2' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group2' then noPK else 0 end),
     max(case when noName = 'value1' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group2' then noPK else 0 end),
     max(case when noName = 'value3' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group3' then noPK else 0 end),
     max(case when noName = 'value4' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group3' then noPK else 0 end),
     max(case when noName = 'value2' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group4' then noPK else 0 end),
     max(case when noName = 'Group1' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group4' then noPK else 0 end),
     max(case when noName = 'Group3' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group5' then noPK else 0 end),
     max(case when noName = 'Group1' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group5' then noPK else 0 end),
     max(case when noName = 'Group2' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group5' then noPK else 0 end),
     max(case when noName = 'value6' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group5' then noPK else 0 end),
     max(case when noName = 'value7' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group6' then noPK else 0 end),
     max(case when noName = 'value9' then noPK else 0 end)
  from netobject;
insert netGroupObject(ngmPKParent,ngmPKChild)
  select max(Case when noName = 'Group6' then noPK else 0 end),
     max(case when noName = 'Group5' then noPK else 0 end)
  from netobject;

Open in new window

0
Comment
Question by:rowansmith
1 Comment
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23645309
Normal ways of displaying hierarchical using TRANSACT-SQL would either involve table-valued UDFs or CTEs, and SQL Server CE supports neither of them..

I guess you will have to use .NET managed code using repetitive SQL statements to loop through records one at a time to retrieve required data above..
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

609 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