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

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;

Select allOpen in new window