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

Solved

Posted on 2009-02-14

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

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;
```

1 Comment

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..

Question has a verified solution.

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

Course of the Month9 days, 12 hours left to enroll

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