We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Medium Priority
1,024 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

Comment
Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.