Professional Web Applications Themes

Hierarchies/Relational Division - Microsoft SQL / MS SQL Server

Hello, I've a problem with hierarchies and relation division. I'd be really grateful for any help on this, I've included all my DDL below with comments to explain the steps. Hope this makes sense! Many thanks, Kevin Munro. [url]www.c3amulet.com[/url] -- Here's a fictitious example of a hierarchy and relational -- division problem. In my app I have more tables than this -- and the data is on a totally different subject but I've created -- these tables to hopefully illustrate my problem in as -- straightforward a way as possible. -- All my staff work in London and my contractors ...

  1. #1

    Default Hierarchies/Relational Division

    Hello, I've a problem with hierarchies and relation division.

    I'd be really grateful for any help on this, I've included all
    my DDL below with comments to explain the steps.

    Hope this makes sense!

    Many thanks,

    Kevin Munro.
    [url]www.c3amulet.com[/url]

    -- Here's a fictitious example of a hierarchy and relational
    -- division problem. In my app I have more tables than this
    -- and the data is on a totally different subject but I've created
    -- these tables to hopefully illustrate my problem in as
    -- straightforward a way as possible.

    -- All my staff work in London and my contractors work in New York.
    -- I want a query that will return all my staff who are
    -- available in London and have oil industry experience.
    -- I can have many properties against each container -
    -- for example, the Staff root level container could
    -- be made up of Location, Salary, Type, Age Range etc...
    -- I know that these properties are entities themselves and should
    -- be represented in their own tables but what I am trying to do
    -- here is to build a generic searching tool over a hierarchy
    -- where the user can 'tag' any number of properties against a
    -- container. The user can define any of these property names
    -- and property values and they can already build up the hierarchy
    -- themselves. I have triggers and stored procedures to maintain
    -- the lvl and hierarchy columns.
    -- Also, in real life I would also have a nodes table - we call
    -- this containers and points in our company but let's just
    -- stick to one tree table for this example...

    drop table tree
    drop table props

    create table tree (
    ident int,
    name varchar(15),
    parent int,
    lvl int,
    hierarchy varchar(50)
    )

    create table props (
    ident int,
    propname varchar(15),
    propvalue varchar (15)
    )

    set nocount on
    -- set up tree structure
    delete from tree
    insert into tree values (100,'Staff',0,0,'.100.')
    insert into tree values (101,'Developers',100,1,'.100.101.')
    insert into tree values (102,'Web',101,2,'.100.101.102.')
    insert into tree values (103,'Desktop',101,2,'.100.101.103.')
    insert into tree values (150,'Managers',100,1,'.100.150.')
    insert into tree values (151,'Projects',150,2,'.100.150.151.')
    insert into tree values (200,'Contracters',0,0,'.200.')
    insert into tree values (201,'Developers',200,1,'.200.201.')
    insert into tree values (202,'Linux',201,2,'.200.201.202.')
    insert into tree values (203,'Web',202,2,'.200.201.203.')

    -- show the hierarchy
    SELECT te(' | ', lvl) + name as people
    from tree order by hierarchy

    -- this should give us a hierarchy of
    -- Staff
    -- | Developers
    -- | | Web
    -- | | Desktop
    -- | Managers
    -- | | Projects
    -- Contracters
    -- | Developers
    -- | | Linux
    -- | | Web

    -- insert the property data
    -- each container can have any number of properties not unique to them
    -- hence I can't express these properties as part of the hierarchy.

    -- this is a simplified property table - in real life I have
    -- further tables to normalise the data.

    delete from props
    insert into props values (100,'Location','London')
    insert into props values (200,'Location','New York')
    insert into props values (102,'Industry','Oil')
    insert into props values (103,'Industry','Oil')
    insert into props values (103,'Industry','Water')

    -- now some more essentially random data to make it a bit more real
    -- and ensure we don't get back false results
    insert into props values (100,'Salary','Grade A')
    insert into props values (102,'Skill','A')
    insert into props values (102,'Experience','C')

    -- So the props table links on a one to many relationship with
    -- the tree table. For any record in the tree table I can have
    -- any number of properties in the props table.

    -- e.g. My web developers in London have these properties...

    select * from props where ident=102

    -- ident propname propvalue
    -- ----------- -------------------- --------------------
    -- 102 Industry Oil
    -- 102 Skill A
    -- 102 Experience C

    -- but to get all my developers in London with Oil Industry
    -- experience I need to do some relational division.

    -- this query gets me all those containers that have either.

    select tree.ident,name,propname,propvalue from tree,props where
    tree.ident=props.ident and
    ((propname='Location' and propvalue='London') or
    (propname='Industry' and propvalue='Oil'))

    -- gives me these results...
    -- ident name propname propvalue
    -- ----------- --------------- --------------- ---------------
    -- 100 Staff Location London
    -- 102 Web Industry Oil
    -- 103 Desktop Industry Oil

    -- and this query...
    select tree.ident,name,propname,propvalue from tree,props where
    tree.ident=props.ident and
    ((propname='Location' and propvalue='London') and
    (propname='Industry' and propvalue='Oil'))

    -- gives me no results...

    -- if these properties were on the same level (such as
    -- developers with Oil industry experience and a Skill of A
    -- then I can do this using relational division...

    -- I'll do this in parts..
    -- Here are the properties I'm intersted in...

    select * from props where
    ((propname='Skill' and propvalue='A') or
    (propname='Industry' and propvalue='Oil'))

    -- from looking at these results it's obvious I'm interested
    -- in ident 102 as this matches both criteria...

    -- ident propname propvalue
    -- ----------- --------------- ---------------
    -- 102 Industry Oil
    -- 103 Industry Oil
    -- 102 Skill A

    -- let's count the idents
    select count(ident) x, ident from props where
    ((propname='Skill' and propvalue='A') or
    (propname='Industry' and propvalue='Oil'))
    group by ident

    -- x ident
    -- ----------- -----------
    -- 2 102
    -- 1 103

    -- ok, I'm intersted in x=2 so let's get the ident
    select ident from
    (select count(ident) x, ident from props where
    ((propname='Skill' and propvalue='A') or
    (propname='Industry' and propvalue='Oil'))
    group by ident) results where x=2

    -- and I can get ident 102
    -- ident
    -- -----------
    -- 102

    -- So my problem is how on earth can I get this query to
    -- return ident 102

    select ident from
    (select count(ident) x, ident from props where
    ((propname='Location' and propvalue='London') or
    (propname='Industry' and propvalue='Oil'))
    group by ident) results where x=2

    -- returns no records because they are at different
    -- hierarchy levels
















    Kevin Munro Guest

  2. #2

    Default Re: Hierarchies/Relational Division

    I think I have it...

    select ident from
    (
    select t.ident from tree t join tree x on t.hierarchy like x.hierarchy+'%'
    where x.ident in
    (select ident from props where propname='Industry' and propvalue='Oil')
    UNION ALL
    select t.ident from tree t join tree x on t.hierarchy like x.hierarchy+'%'
    where x.ident in
    (select ident from props where propname='Location' and propvalue='London')
    ) results group by ident having count(ident)=2

    Now to test on the big database :-)

    Kevin.


    "Kevin Munro" <kevin_remove8839421c3amulet.com> wrote in message
    news:3f0aa84a$0$45175$65c69314mercury.nildram.net ...
    > Hello, I've a problem with hierarchies and relation division.
    >
    > I'd be really grateful for any help on this, I've included all
    > my DDL below with comments to explain the steps.
    >
    > Hope this makes sense!
    >
    > Many thanks,
    >
    > Kevin Munro.
    > [url]www.c3amulet.com[/url]
    >
    > -- Here's a fictitious example of a hierarchy and relational
    > -- division problem. In my app I have more tables than this
    > -- and the data is on a totally different subject but I've created
    > -- these tables to hopefully illustrate my problem in as
    > -- straightforward a way as possible.
    >
    > -- All my staff work in London and my contractors work in New York.
    > -- I want a query that will return all my staff who are
    > -- available in London and have oil industry experience.
    > -- I can have many properties against each container -
    > -- for example, the Staff root level container could
    > -- be made up of Location, Salary, Type, Age Range etc...
    > -- I know that these properties are entities themselves and should
    > -- be represented in their own tables but what I am trying to do
    > -- here is to build a generic searching tool over a hierarchy
    > -- where the user can 'tag' any number of properties against a
    > -- container. The user can define any of these property names
    > -- and property values and they can already build up the hierarchy
    > -- themselves. I have triggers and stored procedures to maintain
    > -- the lvl and hierarchy columns.
    > -- Also, in real life I would also have a nodes table - we call
    > -- this containers and points in our company but let's just
    > -- stick to one tree table for this example...
    >
    > drop table tree
    > drop table props
    >
    > create table tree (
    > ident int,
    > name varchar(15),
    > parent int,
    > lvl int,
    > hierarchy varchar(50)
    > )
    >
    > create table props (
    > ident int,
    > propname varchar(15),
    > propvalue varchar (15)
    > )
    >
    > set nocount on
    > -- set up tree structure
    > delete from tree
    > insert into tree values (100,'Staff',0,0,'.100.')
    > insert into tree values (101,'Developers',100,1,'.100.101.')
    > insert into tree values (102,'Web',101,2,'.100.101.102.')
    > insert into tree values (103,'Desktop',101,2,'.100.101.103.')
    > insert into tree values (150,'Managers',100,1,'.100.150.')
    > insert into tree values (151,'Projects',150,2,'.100.150.151.')
    > insert into tree values (200,'Contracters',0,0,'.200.')
    > insert into tree values (201,'Developers',200,1,'.200.201.')
    > insert into tree values (202,'Linux',201,2,'.200.201.202.')
    > insert into tree values (203,'Web',202,2,'.200.201.203.')
    >
    > -- show the hierarchy
    > SELECT te(' | ', lvl) + name as people
    > from tree order by hierarchy
    >
    > -- this should give us a hierarchy of
    > -- Staff
    > -- | Developers
    > -- | | Web
    > -- | | Desktop
    > -- | Managers
    > -- | | Projects
    > -- Contracters
    > -- | Developers
    > -- | | Linux
    > -- | | Web
    >
    > -- insert the property data
    > -- each container can have any number of properties not unique to them
    > -- hence I can't express these properties as part of the hierarchy.
    >
    > -- this is a simplified property table - in real life I have
    > -- further tables to normalise the data.
    >
    > delete from props
    > insert into props values (100,'Location','London')
    > insert into props values (200,'Location','New York')
    > insert into props values (102,'Industry','Oil')
    > insert into props values (103,'Industry','Oil')
    > insert into props values (103,'Industry','Water')
    >
    > -- now some more essentially random data to make it a bit more real
    > -- and ensure we don't get back false results
    > insert into props values (100,'Salary','Grade A')
    > insert into props values (102,'Skill','A')
    > insert into props values (102,'Experience','C')
    >
    > -- So the props table links on a one to many relationship with
    > -- the tree table. For any record in the tree table I can have
    > -- any number of properties in the props table.
    >
    > -- e.g. My web developers in London have these properties...
    >
    > select * from props where ident=102
    >
    > -- ident propname propvalue
    > -- ----------- -------------------- --------------------
    > -- 102 Industry Oil
    > -- 102 Skill A
    > -- 102 Experience C
    >
    > -- but to get all my developers in London with Oil Industry
    > -- experience I need to do some relational division.
    >
    > -- this query gets me all those containers that have either.
    >
    > select tree.ident,name,propname,propvalue from tree,props where
    > tree.ident=props.ident and
    > ((propname='Location' and propvalue='London') or
    > (propname='Industry' and propvalue='Oil'))
    >
    > -- gives me these results...
    > -- ident name propname propvalue
    > -- ----------- --------------- --------------- ---------------
    > -- 100 Staff Location London
    > -- 102 Web Industry Oil
    > -- 103 Desktop Industry Oil
    >
    > -- and this query...
    > select tree.ident,name,propname,propvalue from tree,props where
    > tree.ident=props.ident and
    > ((propname='Location' and propvalue='London') and
    > (propname='Industry' and propvalue='Oil'))
    >
    > -- gives me no results...
    >
    > -- if these properties were on the same level (such as
    > -- developers with Oil industry experience and a Skill of A
    > -- then I can do this using relational division...
    >
    > -- I'll do this in parts..
    > -- Here are the properties I'm intersted in...
    >
    > select * from props where
    > ((propname='Skill' and propvalue='A') or
    > (propname='Industry' and propvalue='Oil'))
    >
    > -- from looking at these results it's obvious I'm interested
    > -- in ident 102 as this matches both criteria...
    >
    > -- ident propname propvalue
    > -- ----------- --------------- ---------------
    > -- 102 Industry Oil
    > -- 103 Industry Oil
    > -- 102 Skill A
    >
    > -- let's count the idents
    > select count(ident) x, ident from props where
    > ((propname='Skill' and propvalue='A') or
    > (propname='Industry' and propvalue='Oil'))
    > group by ident
    >
    > -- x ident
    > -- ----------- -----------
    > -- 2 102
    > -- 1 103
    >
    > -- ok, I'm intersted in x=2 so let's get the ident
    > select ident from
    > (select count(ident) x, ident from props where
    > ((propname='Skill' and propvalue='A') or
    > (propname='Industry' and propvalue='Oil'))
    > group by ident) results where x=2
    >
    > -- and I can get ident 102
    > -- ident
    > -- -----------
    > -- 102
    >
    > -- So my problem is how on earth can I get this query to
    > -- return ident 102
    >
    > select ident from
    > (select count(ident) x, ident from props where
    > ((propname='Location' and propvalue='London') or
    > (propname='Industry' and propvalue='Oil'))
    > group by ident) results where x=2
    >
    > -- returns no records because they are at different
    > -- hierarchy levels
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >

    Kevin Munro Guest

  3. #3

    Default Re: Hierarchies/Relational Division

    Here is another solution to your problem. How did I approach it: I wanted to
    get for every element
    in the tree (table tree), all of his parents in one join. This way it would
    not matter for algorythm how
    many levels of hierarchy there are int the tree table.

    For exmaple: Web (ident = 102) element has two parents: Staff (100) and
    Developers (101). There for the
    query will give me to records for Web (102) element. All I had to do now was
    to check if proper properties
    had propervalues. Since children have their parents properties than I had to
    check both parents and children
    properties.

    I was not shure what do you wan't to happen if both child and its parent
    have the same property name but different
    values. Which property than has precedence. I'll leave this problem for you
    to solve. All you have to do is to change the
    where clause a little bit.

    Hope this helps.

    select
    Eident = tE.ident
    , Ename = tE.name
    , Epropname = pE.propname
    , Epropvalue = pE.propvalue
    , Tsident = tTs.ident
    , Tsname = tTs.name
    , Tspropname = pTs.propname
    , Tspropvalue = pTs.propvalue
    , *
    from tree tE
    join tree tTs on tTs.Hierarchy like substring(tE.hierarchy, 1,
    charindex('.', reverse(tE.hierarchy), 2)) + '%'
    and tTs.lvl < tE.lvl
    and substring(tE.hierarchy, 1, len(tE.hierarchy) - charindex('.',
    reverse(tE.hierarchy), 2)) + '.' like tTs.hierarchy + '%'
    left join props pE on tE.ident = pE.ident
    left join props pTs on tTs.ident = pTs.ident
    where (pE.propname = 'Industry' and pE.propvalue = 'Oil'
    and pTs.propname = 'Location' and pTs.propvalue = 'London')
    or (pE.propname = 'Location' and pE.propvalue = 'London'
    and pTs.propname = 'Industry' and pTs.propvalue = 'Oil')
    order by tE.ident

    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "Kevin Munro" <kevin_remove8839421c3amulet.com> wrote in message
    news:3f0aa84a$0$45175$65c69314mercury.nildram.net ...
    > Hello, I've a problem with hierarchies and relation division.
    >
    > I'd be really grateful for any help on this, I've included all
    > my DDL below with comments to explain the steps.
    >
    > Hope this makes sense!
    >
    > Many thanks,
    >
    > Kevin Munro.
    > [url]www.c3amulet.com[/url]
    >
    > -- Here's a fictitious example of a hierarchy and relational
    > -- division problem. In my app I have more tables than this
    > -- and the data is on a totally different subject but I've created
    > -- these tables to hopefully illustrate my problem in as
    > -- straightforward a way as possible.
    >
    > -- All my staff work in London and my contractors work in New York.
    > -- I want a query that will return all my staff who are
    > -- available in London and have oil industry experience.
    > -- I can have many properties against each container -
    > -- for example, the Staff root level container could
    > -- be made up of Location, Salary, Type, Age Range etc...
    > -- I know that these properties are entities themselves and should
    > -- be represented in their own tables but what I am trying to do
    > -- here is to build a generic searching tool over a hierarchy
    > -- where the user can 'tag' any number of properties against a
    > -- container. The user can define any of these property names
    > -- and property values and they can already build up the hierarchy
    > -- themselves. I have triggers and stored procedures to maintain
    > -- the lvl and hierarchy columns.
    > -- Also, in real life I would also have a nodes table - we call
    > -- this containers and points in our company but let's just
    > -- stick to one tree table for this example...
    >
    > drop table tree
    > drop table props
    >
    > create table tree (
    > ident int,
    > name varchar(15),
    > parent int,
    > lvl int,
    > hierarchy varchar(50)
    > )
    >
    > create table props (
    > ident int,
    > propname varchar(15),
    > propvalue varchar (15)
    > )
    >
    > set nocount on
    > -- set up tree structure
    > delete from tree
    > insert into tree values (100,'Staff',0,0,'.100.')
    > insert into tree values (101,'Developers',100,1,'.100.101.')
    > insert into tree values (102,'Web',101,2,'.100.101.102.')
    > insert into tree values (103,'Desktop',101,2,'.100.101.103.')
    > insert into tree values (150,'Managers',100,1,'.100.150.')
    > insert into tree values (151,'Projects',150,2,'.100.150.151.')
    > insert into tree values (200,'Contracters',0,0,'.200.')
    > insert into tree values (201,'Developers',200,1,'.200.201.')
    > insert into tree values (202,'Linux',201,2,'.200.201.202.')
    > insert into tree values (203,'Web',202,2,'.200.201.203.')
    >
    > -- show the hierarchy
    > SELECT te(' | ', lvl) + name as people
    > from tree order by hierarchy
    >
    > -- this should give us a hierarchy of
    > -- Staff
    > -- | Developers
    > -- | | Web
    > -- | | Desktop
    > -- | Managers
    > -- | | Projects
    > -- Contracters
    > -- | Developers
    > -- | | Linux
    > -- | | Web
    >
    > -- insert the property data
    > -- each container can have any number of properties not unique to them
    > -- hence I can't express these properties as part of the hierarchy.
    >
    > -- this is a simplified property table - in real life I have
    > -- further tables to normalise the data.
    >
    > delete from props
    > insert into props values (100,'Location','London')
    > insert into props values (200,'Location','New York')
    > insert into props values (102,'Industry','Oil')
    > insert into props values (103,'Industry','Oil')
    > insert into props values (103,'Industry','Water')
    >
    > -- now some more essentially random data to make it a bit more real
    > -- and ensure we don't get back false results
    > insert into props values (100,'Salary','Grade A')
    > insert into props values (102,'Skill','A')
    > insert into props values (102,'Experience','C')
    >
    > -- So the props table links on a one to many relationship with
    > -- the tree table. For any record in the tree table I can have
    > -- any number of properties in the props table.
    >
    > -- e.g. My web developers in London have these properties...
    >
    > select * from props where ident=102
    >
    > -- ident propname propvalue
    > -- ----------- -------------------- --------------------
    > -- 102 Industry Oil
    > -- 102 Skill A
    > -- 102 Experience C
    >
    > -- but to get all my developers in London with Oil Industry
    > -- experience I need to do some relational division.
    >
    > -- this query gets me all those containers that have either.
    >
    > select tree.ident,name,propname,propvalue from tree,props where
    > tree.ident=props.ident and
    > ((propname='Location' and propvalue='London') or
    > (propname='Industry' and propvalue='Oil'))
    >
    > -- gives me these results...
    > -- ident name propname propvalue
    > -- ----------- --------------- --------------- ---------------
    > -- 100 Staff Location London
    > -- 102 Web Industry Oil
    > -- 103 Desktop Industry Oil
    >
    > -- and this query...
    > select tree.ident,name,propname,propvalue from tree,props where
    > tree.ident=props.ident and
    > ((propname='Location' and propvalue='London') and
    > (propname='Industry' and propvalue='Oil'))
    >
    > -- gives me no results...
    >
    > -- if these properties were on the same level (such as
    > -- developers with Oil industry experience and a Skill of A
    > -- then I can do this using relational division...
    >
    > -- I'll do this in parts..
    > -- Here are the properties I'm intersted in...
    >
    > select * from props where
    > ((propname='Skill' and propvalue='A') or
    > (propname='Industry' and propvalue='Oil'))
    >
    > -- from looking at these results it's obvious I'm interested
    > -- in ident 102 as this matches both criteria...
    >
    > -- ident propname propvalue
    > -- ----------- --------------- ---------------
    > -- 102 Industry Oil
    > -- 103 Industry Oil
    > -- 102 Skill A
    >
    > -- let's count the idents
    > select count(ident) x, ident from props where
    > ((propname='Skill' and propvalue='A') or
    > (propname='Industry' and propvalue='Oil'))
    > group by ident
    >
    > -- x ident
    > -- ----------- -----------
    > -- 2 102
    > -- 1 103
    >
    > -- ok, I'm intersted in x=2 so let's get the ident
    > select ident from
    > (select count(ident) x, ident from props where
    > ((propname='Skill' and propvalue='A') or
    > (propname='Industry' and propvalue='Oil'))
    > group by ident) results where x=2
    >
    > -- and I can get ident 102
    > -- ident
    > -- -----------
    > -- 102
    >
    > -- So my problem is how on earth can I get this query to
    > -- return ident 102
    >
    > select ident from
    > (select count(ident) x, ident from props where
    > ((propname='Location' and propvalue='London') or
    > (propname='Industry' and propvalue='Oil'))
    > group by ident) results where x=2
    >
    > -- returns no records because they are at different
    > -- hierarchy levels
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >

    Dean Savovic Guest

  4. #4

    Default Re: Hierarchies/Relational Division

    Dean, many thanks for this. It's a view of the problem that had never even
    occurred to me. I'll need to study it and then apply this idea to my large
    database.

    Kevin.



    "Dean Savovic" <dsavovicinet.hr> wrote in message
    news:begi8k$j1u$1brown.net4u.hr...
    > You are right! I missed out a situation where element has no properties
    but
    > his parents have them.
    > So I rewrote my code in that way that now there is a function that for
    given
    > ident (element in the tree)
    > returns a ";" delimited string containing all properties and their names.
    >
    > For example:
    >
    > for element Web (102)
    > 102 Web .100.101.102.
    > string looks like this:
    > Industry=Oil;Location=London;Industry=Oil;Salary=G rade
    > A;Skill=A;Location=London;Skill=A;Salary=Grade
    > A;Experience=C;Location=London;Experience=C;Salary =Grade
    > A;Industry=Oil;Skill=A;Experience=C;
    >
    > Here is the function:
    >
    > create function PropsAsString (Eident int)
    > returns varchar(8000)
    > as
    > begin
    > declare s varchar(8000)
    >
    > set s = ''
    >
    > select
    > s = s + IsNull(pE.propname + '=' + pE.propvalue + ';', '') +
    > IsNull(pTs.propname + '=' + pTs.propvalue + ';', '')
    > from tree tE
    > join tree tTs on tTs.Hierarchy like substring(tE.hierarchy, 1,
    > charindex('.', reverse(tE.hierarchy), 2)) + '%'
    > and tTs.lvl < tE.lvl
    > and substring(tE.hierarchy, 1, len(tE.hierarchy) - charindex('.',
    > reverse(tE.hierarchy), 2)) + '.' like tTs.hierarchy + '%'
    > left join props pE on tE.ident = pE.ident
    > left join props pTs on tTs.ident = pTs.ident
    > where tE.ident = eident
    >
    > return s
    > end
    >
    > When using this function, select statement that gives the wanted result is
    > very simple. Still there is no limit to number of hierarchy levels
    > and number of properties in the where statements.
    >
    > select
    > *
    > , props = dbo.PropsAsString(ident)
    > from tree
    > where dbo.PropsAsString(ident) like '%Location=London;%'
    > and dbo.PropsAsString(ident) like '%Industry=Oil;%'
    >
    > --
    > Dean Savovic
    > [url]www.teched.hr[/url]
    >
    >
    > "Delbert Glass" <delbertnoincoming.com> wrote in message
    > news:#a5Kd2WRDHA.2232TK2MSFTNGP11.phx.gbl...
    > >
    > > Add this data:
    > >
    > > insert into tree values (104,'How about me?',103,3,'.100.101.103.104.')
    > > insert into tree values (105,'How about me
    > > 2?',104,4,'.100.101.103.104.105.')
    > >
    > > and then compare the (different) results.
    > >
    > >
    > > I wonder:
    > > Is the additional data outside his domain
    > > and your query is within his domain
    > > or vis versa.
    > >
    > > Bye,
    > > Delbert Glass
    > >
    > > "Dean Savovic" <dsavovicinet.hr> wrote in message
    > > news:beeke0$ksa$1brown.net4u.hr...
    > > > Here is another solution to your problem. How did I approach it: I
    > wanted
    > > to
    > > > get for every element
    > > > in the tree (table tree), all of his parents in one join. This way it
    > > would
    > > > not matter for algorythm how
    > > > many levels of hierarchy there are int the tree table.
    > > >
    > > > For exmaple: Web (ident = 102) element has two parents: Staff (100)
    and
    > > > Developers (101). There for the
    > > > query will give me to records for Web (102) element. All I had to do
    now
    > > was
    > > > to check if proper properties
    > > > had propervalues. Since children have their parents properties than I
    > had
    > > to
    > > > check both parents and children
    > > > properties.
    > > >
    > > > I was not shure what do you wan't to happen if both child and its
    parent
    > > > have the same property name but different
    > > > values. Which property than has precedence. I'll leave this problem
    for
    > > you
    > > > to solve. All you have to do is to change the
    > > > where clause a little bit.
    > > >
    > > > Hope this helps.
    > > >
    > > > select
    > > > Eident = tE.ident
    > > > , Ename = tE.name
    > > > , Epropname = pE.propname
    > > > , Epropvalue = pE.propvalue
    > > > , Tsident = tTs.ident
    > > > , Tsname = tTs.name
    > > > , Tspropname = pTs.propname
    > > > , Tspropvalue = pTs.propvalue
    > > > , *
    > > > from tree tE
    > > > join tree tTs on tTs.Hierarchy like substring(tE.hierarchy, 1,
    > > > charindex('.', reverse(tE.hierarchy), 2)) + '%'
    > > > and tTs.lvl < tE.lvl
    > > > and substring(tE.hierarchy, 1, len(tE.hierarchy) - charindex('.',
    > > > reverse(tE.hierarchy), 2)) + '.' like tTs.hierarchy + '%'
    > > > left join props pE on tE.ident = pE.ident
    > > > left join props pTs on tTs.ident = pTs.ident
    > > > where (pE.propname = 'Industry' and pE.propvalue = 'Oil'
    > > > and pTs.propname = 'Location' and pTs.propvalue = 'London')
    > > > or (pE.propname = 'Location' and pE.propvalue = 'London'
    > > > and pTs.propname = 'Industry' and pTs.propvalue = 'Oil')
    > > > order by tE.ident
    > > >
    > > > --
    > > > Dean Savovic
    > > > [url]www.teched.hr[/url]
    > > >
    > > >
    > > > "Kevin Munro" <kevin_remove8839421c3amulet.com> wrote in message
    > > > news:3f0aa84a$0$45175$65c69314mercury.nildram.net ...
    > > > > Hello, I've a problem with hierarchies and relation division.
    > > > >
    > > > > I'd be really grateful for any help on this, I've included all
    > > > > my DDL below with comments to explain the steps.
    > > > >
    > > > > Hope this makes sense!
    > > > >
    > > > > Many thanks,
    > > > >
    > > > > Kevin Munro.
    > > > > [url]www.c3amulet.com[/url]
    > > > >
    > > > > -- Here's a fictitious example of a hierarchy and relational
    > > > > -- division problem. In my app I have more tables than this
    > > > > -- and the data is on a totally different subject but I've created
    > > > > -- these tables to hopefully illustrate my problem in as
    > > > > -- straightforward a way as possible.
    > > > >
    > > > > -- All my staff work in London and my contractors work in New York.
    > > > > -- I want a query that will return all my staff who are
    > > > > -- available in London and have oil industry experience.
    > > > > -- I can have many properties against each container -
    > > > > -- for example, the Staff root level container could
    > > > > -- be made up of Location, Salary, Type, Age Range etc...
    > > > > -- I know that these properties are entities themselves and should
    > > > > -- be represented in their own tables but what I am trying to do
    > > > > -- here is to build a generic searching tool over a hierarchy
    > > > > -- where the user can 'tag' any number of properties against a
    > > > > -- container. The user can define any of these property names
    > > > > -- and property values and they can already build up the hierarchy
    > > > > -- themselves. I have triggers and stored procedures to maintain
    > > > > -- the lvl and hierarchy columns.
    > > > > -- Also, in real life I would also have a nodes table - we call
    > > > > -- this containers and points in our company but let's just
    > > > > -- stick to one tree table for this example...
    > > > >
    > > > > drop table tree
    > > > > drop table props
    > > > >
    > > > > create table tree (
    > > > > ident int,
    > > > > name varchar(15),
    > > > > parent int,
    > > > > lvl int,
    > > > > hierarchy varchar(50)
    > > > > )
    > > > >
    > > > > create table props (
    > > > > ident int,
    > > > > propname varchar(15),
    > > > > propvalue varchar (15)
    > > > > )
    > > > >
    > > > > set nocount on
    > > > > -- set up tree structure
    > > > > delete from tree
    > > > > insert into tree values (100,'Staff',0,0,'.100.')
    > > > > insert into tree values (101,'Developers',100,1,'.100.101.')
    > > > > insert into tree values (102,'Web',101,2,'.100.101.102.')
    > > > > insert into tree values (103,'Desktop',101,2,'.100.101.103.')
    > > > > insert into tree values (150,'Managers',100,1,'.100.150.')
    > > > > insert into tree values (151,'Projects',150,2,'.100.150.151.')
    > > > > insert into tree values (200,'Contracters',0,0,'.200.')
    > > > > insert into tree values (201,'Developers',200,1,'.200.201.')
    > > > > insert into tree values (202,'Linux',201,2,'.200.201.202.')
    > > > > insert into tree values (203,'Web',202,2,'.200.201.203.')
    > > > >
    > > > > -- show the hierarchy
    > > > > SELECT te(' | ', lvl) + name as people
    > > > > from tree order by hierarchy
    > > > >
    > > > > -- this should give us a hierarchy of
    > > > > -- Staff
    > > > > -- | Developers
    > > > > -- | | Web
    > > > > -- | | Desktop
    > > > > -- | Managers
    > > > > -- | | Projects
    > > > > -- Contracters
    > > > > -- | Developers
    > > > > -- | | Linux
    > > > > -- | | Web
    > > > >
    > > > > -- insert the property data
    > > > > -- each container can have any number of properties not unique to
    them
    > > > > -- hence I can't express these properties as part of the hierarchy.
    > > > >
    > > > > -- this is a simplified property table - in real life I have
    > > > > -- further tables to normalise the data.
    > > > >
    > > > > delete from props
    > > > > insert into props values (100,'Location','London')
    > > > > insert into props values (200,'Location','New York')
    > > > > insert into props values (102,'Industry','Oil')
    > > > > insert into props values (103,'Industry','Oil')
    > > > > insert into props values (103,'Industry','Water')
    > > > >
    > > > > -- now some more essentially random data to make it a bit more real
    > > > > -- and ensure we don't get back false results
    > > > > insert into props values (100,'Salary','Grade A')
    > > > > insert into props values (102,'Skill','A')
    > > > > insert into props values (102,'Experience','C')
    > > > >
    > > > > -- So the props table links on a one to many relationship with
    > > > > -- the tree table. For any record in the tree table I can have
    > > > > -- any number of properties in the props table.
    > > > >
    > > > > -- e.g. My web developers in London have these properties...
    > > > >
    > > > > select * from props where ident=102
    > > > >
    > > > > -- ident propname propvalue
    > > > > -- ----------- -------------------- --------------------
    > > > > -- 102 Industry Oil
    > > > > -- 102 Skill A
    > > > > -- 102 Experience C
    > > > >
    > > > > -- but to get all my developers in London with Oil Industry
    > > > > -- experience I need to do some relational division.
    > > > >
    > > > > -- this query gets me all those containers that have either.
    > > > >
    > > > > select tree.ident,name,propname,propvalue from tree,props where
    > > > > tree.ident=props.ident and
    > > > > ((propname='Location' and propvalue='London') or
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > >
    > > > > -- gives me these results...
    > > > > -- ident name propname propvalue
    > > > > -- ----------- --------------- --------------- ---------------
    > > > > -- 100 Staff Location London
    > > > > -- 102 Web Industry Oil
    > > > > -- 103 Desktop Industry Oil
    > > > >
    > > > > -- and this query...
    > > > > select tree.ident,name,propname,propvalue from tree,props where
    > > > > tree.ident=props.ident and
    > > > > ((propname='Location' and propvalue='London') and
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > >
    > > > > -- gives me no results...
    > > > >
    > > > > -- if these properties were on the same level (such as
    > > > > -- developers with Oil industry experience and a Skill of A
    > > > > -- then I can do this using relational division...
    > > > >
    > > > > -- I'll do this in parts..
    > > > > -- Here are the properties I'm intersted in...
    > > > >
    > > > > select * from props where
    > > > > ((propname='Skill' and propvalue='A') or
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > >
    > > > > -- from looking at these results it's obvious I'm interested
    > > > > -- in ident 102 as this matches both criteria...
    > > > >
    > > > > -- ident propname propvalue
    > > > > -- ----------- --------------- ---------------
    > > > > -- 102 Industry Oil
    > > > > -- 103 Industry Oil
    > > > > -- 102 Skill A
    > > > >
    > > > > -- let's count the idents
    > > > > select count(ident) x, ident from props where
    > > > > ((propname='Skill' and propvalue='A') or
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > > group by ident
    > > > >
    > > > > -- x ident
    > > > > -- ----------- -----------
    > > > > -- 2 102
    > > > > -- 1 103
    > > > >
    > > > > -- ok, I'm intersted in x=2 so let's get the ident
    > > > > select ident from
    > > > > (select count(ident) x, ident from props where
    > > > > ((propname='Skill' and propvalue='A') or
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > > group by ident) results where x=2
    > > > >
    > > > > -- and I can get ident 102
    > > > > -- ident
    > > > > -- -----------
    > > > > -- 102
    > > > >
    > > > > -- So my problem is how on earth can I get this query to
    > > > > -- return ident 102
    > > > >
    > > > > select ident from
    > > > > (select count(ident) x, ident from props where
    > > > > ((propname='Location' and propvalue='London') or
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > > group by ident) results where x=2
    > > > >
    > > > > -- returns no records because they are at different
    > > > > -- hierarchy levels
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Kevin Munro Guest

  5. #5

    Default Re: Hierarchies/Relational Division


    While the tree depth itself is directly unlimited,
    the total length of properties and values are limited
    due to your function.
    So if one has properties and values at each level,
    the tree depth is indirectly limited.
    (But the limit is not enforced rather your function will malfunction.)

    -----

    Oops:
    where dbo.PropsAsString(ident) like '%Location=London;%'
    and dbo.PropsAsString(ident) like '%Industry=Oil;%'

    Consider, you'll get an unwanted matches on things like:
    OldLocation=London

    -----

    (for the whole thread)
    If the property of interest is restricted to appearing
    at a specific level only, one could take advantage of that.

    Bye,
    Delbert Glass


    Delbert Glass Guest

  6. #6

    Default Re: Hierarchies/Relational Division

    Although my non fictitious query is futher complicated by there being a few
    more tables and some more joining...

    SELECT 76486479 AS SESSIONID,CONTAINERID,PROPERTYNAME,VALUE,LVL AS POSITION
    FROM PROPERTIESLINK l,PROPERTIES p, CONTAINER C where l.propertyid=p.prpid
    AND C.IDENT=L.CONTAINERID AND PROPERTYID IN (-1,31,22,4,23,35,33) AND ident
    in
    (select c.ident from container c join container x on x.hierarchy like
    c.hierarchy+'%' where x.ident in
    (select ident from
    (select t.ident from container t join container x on t.hierarchy like
    x.hierarchy+'%' where x.ident in
    (select containerid from propertieslink where PROPERTYID=31 AND VALUE =
    'Station Central')
    union all
    select t.ident from container t join container x on t.hierarchy like
    x.hierarchy+'%' where x.ident in
    (select containerid from propertieslink where PROPERTYID=32 AND VALUE =
    'CEMENT') )
    fred group by ident having count(ident)=2))



    kEVIN.



    "Kevin Munro" <kevin_remove8839421c3amulet.com> wrote in message
    news:3f0c3666$0$45172$65c69314mercury.nildram.net ...
    > Delbert, I've stuck with my union solution as I understand it better.
    >
    > select ident from
    > (
    > select t.ident from tree t join tree x on t.hierarchy like x.hierarchy+'%'
    > where x.ident in
    > (select ident from props where propname='Industry' and propvalue='Oil')
    > UNION ALL
    > select t.ident from tree t join tree x on t.hierarchy like x.hierarchy+'%'
    > where x.ident in
    > (select ident from props where propname='Location' and
    propvalue='London')
    > ) results group by ident having count
    >
    > Performance on a 500K row database seems ok but when the query slows down
    to
    > about 40 seconds the bottleneck seems to be on the first row before either
    > side of the union.
    >
    > I seemed to have overlooked your original reply - I'll see how this
    performs
    > though I suspect it will be much the same.
    >
    > Kevin.
    >
    >
    > "Delbert Glass" <delbertnoincoming.com> wrote in message
    > news:e0S5qyiRDHA.2232TK2MSFTNGP11.phx.gbl...
    > >
    > > While the tree depth itself is directly unlimited,
    > > the total length of properties and values are limited
    > > due to your function.
    > > So if one has properties and values at each level,
    > > the tree depth is indirectly limited.
    > > (But the limit is not enforced rather your function will malfunction.)
    > >
    > > -----
    > >
    > > Oops:
    > > where dbo.PropsAsString(ident) like '%Location=London;%'
    > > and dbo.PropsAsString(ident) like '%Industry=Oil;%'
    > >
    > > Consider, you'll get an unwanted matches on things like:
    > > OldLocation=London
    > >
    > > -----
    > >
    > > (for the whole thread)
    > > If the property of interest is restricted to appearing
    > > at a specific level only, one could take advantage of that.
    > >
    > > Bye,
    > > Delbert Glass
    > >
    > >
    >
    >
    >

    Kevin Munro Guest

  7. #7

    Default Re: Hierarchies/Relational Division

    Here are two more.
    Don't forget to include reasonable indexing including clustering.

    select
    -- distinct -- <== include if ident/propname/propvalue are not unique
    ident
    from
    tree,
    (
    select
    (select hierarchy from tree where tree.ident = props.ident) as
    hierarchy
    from props where propname='Industry' and propvalue='Oil'
    ) Industry,
    (
    select
    (select hierarchy from tree where tree.ident = props.ident) as
    hierarchy
    from props where propname='Location' and propvalue='London'
    ) Location
    where tree.hierarchy like Industry.hierarchy+'%'
    and tree.hierarchy like Location.hierarchy+'%'

    select
    -- distinct -- <== add or move distinct here to handle qualifying more
    then once.
    ident
    from
    tree,
    (
    select distinct
    case
    when len(Industry.hierarchy) - len(replace(Industry.hierarchy,'.',''))
    >
    len(Industry.hierarchy) - len(replace(Industry.hierarchy,'.',''))
    then Location.hierarchy
    else Industry.hierarchy
    end as hierarchy
    from
    (
    select
    (select hierarchy from tree where tree.ident = props.ident) as
    hierarchy
    from props where propname='Industry' and propvalue='Oil'
    ) Industry
    inner join
    (
    select
    (select hierarchy from tree where tree.ident = props.ident) as
    hierarchy
    from props where propname='Location' and propvalue='London'
    ) Location
    on Industry.hierarchy like Location.hierarchy+'%'
    or Location.hierarchy like Industry.hierarchy+'%'
    ) Here
    where tree.hierarchy like Here.hierarchy+'%'

    Bye,
    Delbert Glass

    "Delbert Glass" <delbertnoincoming.com> wrote in message
    news:urVORdWRDHA.3768tk2msftngp13.phx.gbl...
    >
    > select tree1.ident
    > from tree tree1
    > where
    > (
    > exists (
    > select *
    > from props
    > where props.ident in (
    > select tree2.ident
    > from tree tree2
    > where tree1.hierarchy like tree2.hierarchy + '%'
    > )
    > and props.propname = 'Location' and props.propvalue = 'London'
    > )
    > )
    > and
    > (
    > exists (
    > select *
    > from props
    > where props.ident in (
    > select tree2.ident
    > from tree tree2
    > where tree1.hierarchy like tree2.hierarchy + '%'
    > )
    > and propname = 'Industry' and propvalue = 'Oil'
    > )
    > )
    >
    > Bye,
    > Delbert Glass
    >
    > "Kevin Munro" <kevin_remove8839421c3amulet.com> wrote in message
    > news:3f0ac89e$0$45176$65c69314mercury.nildram.net ...
    > > I think I have it...
    > >
    > > select ident from
    > > (
    > > select t.ident from tree t join tree x on t.hierarchy like
    x.hierarchy+'%'
    > > where x.ident in
    > > (select ident from props where propname='Industry' and propvalue='Oil')
    > > UNION ALL
    > > select t.ident from tree t join tree x on t.hierarchy like
    x.hierarchy+'%'
    > > where x.ident in
    > > (select ident from props where propname='Location' and
    > propvalue='London')
    > > ) results group by ident having count(ident)=2
    > >
    > > Now to test on the big database :-)
    > >
    > > Kevin.
    > >
    > >
    > > "Kevin Munro" <kevin_remove8839421c3amulet.com> wrote in message
    > > news:3f0aa84a$0$45175$65c69314mercury.nildram.net ...
    > > > Hello, I've a problem with hierarchies and relation division.
    > > >
    > > > I'd be really grateful for any help on this, I've included all
    > > > my DDL below with comments to explain the steps.
    > > >
    > > > Hope this makes sense!
    > > >
    > > > Many thanks,
    > > >
    > > > Kevin Munro.
    > > > [url]www.c3amulet.com[/url]
    > > >
    > > > -- Here's a fictitious example of a hierarchy and relational
    > > > -- division problem. In my app I have more tables than this
    > > > -- and the data is on a totally different subject but I've created
    > > > -- these tables to hopefully illustrate my problem in as
    > > > -- straightforward a way as possible.
    > > >
    > > > -- All my staff work in London and my contractors work in New York.
    > > > -- I want a query that will return all my staff who are
    > > > -- available in London and have oil industry experience.
    > > > -- I can have many properties against each container -
    > > > -- for example, the Staff root level container could
    > > > -- be made up of Location, Salary, Type, Age Range etc...
    > > > -- I know that these properties are entities themselves and should
    > > > -- be represented in their own tables but what I am trying to do
    > > > -- here is to build a generic searching tool over a hierarchy
    > > > -- where the user can 'tag' any number of properties against a
    > > > -- container. The user can define any of these property names
    > > > -- and property values and they can already build up the hierarchy
    > > > -- themselves. I have triggers and stored procedures to maintain
    > > > -- the lvl and hierarchy columns.
    > > > -- Also, in real life I would also have a nodes table - we call
    > > > -- this containers and points in our company but let's just
    > > > -- stick to one tree table for this example...
    > > >
    > > > drop table tree
    > > > drop table props
    > > >
    > > > create table tree (
    > > > ident int,
    > > > name varchar(15),
    > > > parent int,
    > > > lvl int,
    > > > hierarchy varchar(50)
    > > > )
    > > >
    > > > create table props (
    > > > ident int,
    > > > propname varchar(15),
    > > > propvalue varchar (15)
    > > > )
    > > >
    > > > set nocount on
    > > > -- set up tree structure
    > > > delete from tree
    > > > insert into tree values (100,'Staff',0,0,'.100.')
    > > > insert into tree values (101,'Developers',100,1,'.100.101.')
    > > > insert into tree values (102,'Web',101,2,'.100.101.102.')
    > > > insert into tree values (103,'Desktop',101,2,'.100.101.103.')
    > > > insert into tree values (150,'Managers',100,1,'.100.150.')
    > > > insert into tree values (151,'Projects',150,2,'.100.150.151.')
    > > > insert into tree values (200,'Contracters',0,0,'.200.')
    > > > insert into tree values (201,'Developers',200,1,'.200.201.')
    > > > insert into tree values (202,'Linux',201,2,'.200.201.202.')
    > > > insert into tree values (203,'Web',202,2,'.200.201.203.')
    > > >
    > > > -- show the hierarchy
    > > > SELECT te(' | ', lvl) + name as people
    > > > from tree order by hierarchy
    > > >
    > > > -- this should give us a hierarchy of
    > > > -- Staff
    > > > -- | Developers
    > > > -- | | Web
    > > > -- | | Desktop
    > > > -- | Managers
    > > > -- | | Projects
    > > > -- Contracters
    > > > -- | Developers
    > > > -- | | Linux
    > > > -- | | Web
    > > >
    > > > -- insert the property data
    > > > -- each container can have any number of properties not unique to them
    > > > -- hence I can't express these properties as part of the hierarchy.
    > > >
    > > > -- this is a simplified property table - in real life I have
    > > > -- further tables to normalise the data.
    > > >
    > > > delete from props
    > > > insert into props values (100,'Location','London')
    > > > insert into props values (200,'Location','New York')
    > > > insert into props values (102,'Industry','Oil')
    > > > insert into props values (103,'Industry','Oil')
    > > > insert into props values (103,'Industry','Water')
    > > >
    > > > -- now some more essentially random data to make it a bit more real
    > > > -- and ensure we don't get back false results
    > > > insert into props values (100,'Salary','Grade A')
    > > > insert into props values (102,'Skill','A')
    > > > insert into props values (102,'Experience','C')
    > > >
    > > > -- So the props table links on a one to many relationship with
    > > > -- the tree table. For any record in the tree table I can have
    > > > -- any number of properties in the props table.
    > > >
    > > > -- e.g. My web developers in London have these properties...
    > > >
    > > > select * from props where ident=102
    > > >
    > > > -- ident propname propvalue
    > > > -- ----------- -------------------- --------------------
    > > > -- 102 Industry Oil
    > > > -- 102 Skill A
    > > > -- 102 Experience C
    > > >
    > > > -- but to get all my developers in London with Oil Industry
    > > > -- experience I need to do some relational division.
    > > >
    > > > -- this query gets me all those containers that have either.
    > > >
    > > > select tree.ident,name,propname,propvalue from tree,props where
    > > > tree.ident=props.ident and
    > > > ((propname='Location' and propvalue='London') or
    > > > (propname='Industry' and propvalue='Oil'))
    > > >
    > > > -- gives me these results...
    > > > -- ident name propname propvalue
    > > > -- ----------- --------------- --------------- ---------------
    > > > -- 100 Staff Location London
    > > > -- 102 Web Industry Oil
    > > > -- 103 Desktop Industry Oil
    > > >
    > > > -- and this query...
    > > > select tree.ident,name,propname,propvalue from tree,props where
    > > > tree.ident=props.ident and
    > > > ((propname='Location' and propvalue='London') and
    > > > (propname='Industry' and propvalue='Oil'))
    > > >
    > > > -- gives me no results...
    > > >
    > > > -- if these properties were on the same level (such as
    > > > -- developers with Oil industry experience and a Skill of A
    > > > -- then I can do this using relational division...
    > > >
    > > > -- I'll do this in parts..
    > > > -- Here are the properties I'm intersted in...
    > > >
    > > > select * from props where
    > > > ((propname='Skill' and propvalue='A') or
    > > > (propname='Industry' and propvalue='Oil'))
    > > >
    > > > -- from looking at these results it's obvious I'm interested
    > > > -- in ident 102 as this matches both criteria...
    > > >
    > > > -- ident propname propvalue
    > > > -- ----------- --------------- ---------------
    > > > -- 102 Industry Oil
    > > > -- 103 Industry Oil
    > > > -- 102 Skill A
    > > >
    > > > -- let's count the idents
    > > > select count(ident) x, ident from props where
    > > > ((propname='Skill' and propvalue='A') or
    > > > (propname='Industry' and propvalue='Oil'))
    > > > group by ident
    > > >
    > > > -- x ident
    > > > -- ----------- -----------
    > > > -- 2 102
    > > > -- 1 103
    > > >
    > > > -- ok, I'm intersted in x=2 so let's get the ident
    > > > select ident from
    > > > (select count(ident) x, ident from props where
    > > > ((propname='Skill' and propvalue='A') or
    > > > (propname='Industry' and propvalue='Oil'))
    > > > group by ident) results where x=2
    > > >
    > > > -- and I can get ident 102
    > > > -- ident
    > > > -- -----------
    > > > -- 102
    > > >
    > > > -- So my problem is how on earth can I get this query to
    > > > -- return ident 102
    > > >
    > > > select ident from
    > > > (select count(ident) x, ident from props where
    > > > ((propname='Location' and propvalue='London') or
    > > > (propname='Industry' and propvalue='Oil'))
    > > > group by ident) results where x=2
    > > >
    > > > -- returns no records because they are at different
    > > > -- hierarchy levels
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Delbert Glass Guest

  8. #8

    Default Re: Hierarchies/Relational Division

    Container??!! In SQL?? You are forcing an OO model into a relational
    database. This is Sooooooooo wrong, I am not sure where to start. I
    need to write a book on this topic ...

    Apparently you have developers who have skills, and projects which have
    managers (or is it the other way around?) and contractors which are a
    flavor of developer. Why are totally different attributes in same
    hierarchy? Look at it! You show a project as a kind of "staff memmber"
    -- I don't believe that is right.

    Without specs, here is a more relational approach:

    CREATE TABLE Staff
    (emp_nbr INTEGER NOT NULL PRIMARY KEY,
    emp_name VARCHAR(35) NOT NULL,
    job_title CHAR(15 NOT NULL,
    location CHAR(15) NOT NULL, The user can define any of these property
    names
    -- and property values and they can already build up the hierarchy
    -- themselves.
    employment_type NOT NULL
    CHECK (employment_type IN ('contract', 'hire')),
    ...);

    CREATE TABLE Skills
    (emp_nbr INTEGER NOT NULL
    REFERENCES Staff(emp_nbr),
    skill_code INTEGER NOT NULL,
    skill_level INTEGER NOT NULL,
    ...
    PRIMARY KEY (emp_nbr, skill_code));
    >> a generic searching tool over a hierarchy -- where the user can 'tag'
    any number of properties against a -- container. The user can define any
    of these property names
    -- and property values and they can already build up the hierarchy --
    themselves. <<

    In short, you have not done a data model and you expect the user to
    build one for you on the fly. This does not work. It is the DB
    equivalent of a contractor handing lumber to a client and telling him he
    can build his own home and there is no blueprint.

    Oh, the nested sets model or the path enumeration models are better for
    hierarchies that require queires on their structure.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Joe Celko Guest

  9. #9

    Default Re: Hierarchies/Relational Division


    Gee, no lecture on with is/is-not relational division. ;-(

    Bye,
    Delbert Glass


    Delbert Glass Guest

  10. #10

    Default Re: Hierarchies/Relational Division

    Yes, we all know that his database model is wrong, but he is stuck with it,
    so help the man out or....

    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "Joe Celko" <anonymousdevdex.com> wrote in message
    news:u09QoNnRDHA.2240TK2MSFTNGP11.phx.gbl...
    > Container??!! In SQL?? You are forcing an OO model into a relational
    > database. This is Sooooooooo wrong, I am not sure where to start. I
    > need to write a book on this topic ...
    >
    > Apparently you have developers who have skills, and projects which have
    > managers (or is it the other way around?) and contractors which are a
    > flavor of developer. Why are totally different attributes in same
    > hierarchy? Look at it! You show a project as a kind of "staff memmber"
    > -- I don't believe that is right.
    >
    > Without specs, here is a more relational approach:
    >
    > CREATE TABLE Staff
    > (emp_nbr INTEGER NOT NULL PRIMARY KEY,
    > emp_name VARCHAR(35) NOT NULL,
    > job_title CHAR(15 NOT NULL,
    > location CHAR(15) NOT NULL, The user can define any of these property
    > names
    > -- and property values and they can already build up the hierarchy
    > -- themselves.
    > employment_type NOT NULL
    > CHECK (employment_type IN ('contract', 'hire')),
    > ...);
    >
    > CREATE TABLE Skills
    > (emp_nbr INTEGER NOT NULL
    > REFERENCES Staff(emp_nbr),
    > skill_code INTEGER NOT NULL,
    > skill_level INTEGER NOT NULL,
    > ...
    > PRIMARY KEY (emp_nbr, skill_code));
    >
    > >> a generic searching tool over a hierarchy -- where the user can 'tag'
    > any number of properties against a -- container. The user can define any
    > of these property names
    > -- and property values and they can already build up the hierarchy --
    > themselves. <<
    >
    > In short, you have not done a data model and you expect the user to
    > build one for you on the fly. This does not work. It is the DB
    > equivalent of a contractor handing lumber to a client and telling him he
    > can build his own home and there is no blueprint.
    >
    > Oh, the nested sets model or the path enumeration models are better for
    > hierarchies that require queires on their structure.
    >
    > --CELKO--
    > ===========================
    > Please post DDL, so that people do not have to guess what the keys,
    > constraints, Declarative Referential Integrity, datatypes, etc. in your
    > schema are.
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    deeeoo Guest

  11. #11

    Default Re: Hierarchies/Relational Division

    Thanks Delbert, I follow the first one but haven't got my head around the
    second one yet.
    The first one looks very similar to the union solution.

    Kevin.

    "Delbert Glass" <delbertnoincoming.com> wrote in message
    news:%231KXailRDHA.2332TK2MSFTNGP10.phx.gbl...
    > Here are two more.
    > Don't forget to include reasonable indexing including clustering.
    >
    > select
    > -- distinct -- <== include if ident/propname/propvalue are not unique
    > ident
    > from
    > tree,
    > (
    > select
    > (select hierarchy from tree where tree.ident = props.ident) as
    > hierarchy
    > from props where propname='Industry' and propvalue='Oil'
    > ) Industry,
    > (
    > select
    > (select hierarchy from tree where tree.ident = props.ident) as
    > hierarchy
    > from props where propname='Location' and propvalue='London'
    > ) Location
    > where tree.hierarchy like Industry.hierarchy+'%'
    > and tree.hierarchy like Location.hierarchy+'%'
    >
    > select
    > -- distinct -- <== add or move distinct here to handle qualifying more
    > then once.
    > ident
    > from
    > tree,
    > (
    > select distinct
    > case
    > when len(Industry.hierarchy) - len(replace(Industry.hierarchy,'.',''))
    > >
    > len(Industry.hierarchy) - len(replace(Industry.hierarchy,'.',''))
    > then Location.hierarchy
    > else Industry.hierarchy
    > end as hierarchy
    > from
    > (
    > select
    > (select hierarchy from tree where tree.ident = props.ident) as
    > hierarchy
    > from props where propname='Industry' and propvalue='Oil'
    > ) Industry
    > inner join
    > (
    > select
    > (select hierarchy from tree where tree.ident = props.ident) as
    > hierarchy
    > from props where propname='Location' and propvalue='London'
    > ) Location
    > on Industry.hierarchy like Location.hierarchy+'%'
    > or Location.hierarchy like Industry.hierarchy+'%'
    > ) Here
    > where tree.hierarchy like Here.hierarchy+'%'
    >
    > Bye,
    > Delbert Glass
    >
    > "Delbert Glass" <delbertnoincoming.com> wrote in message
    > news:urVORdWRDHA.3768tk2msftngp13.phx.gbl...
    > >
    > > select tree1.ident
    > > from tree tree1
    > > where
    > > (
    > > exists (
    > > select *
    > > from props
    > > where props.ident in (
    > > select tree2.ident
    > > from tree tree2
    > > where tree1.hierarchy like tree2.hierarchy + '%'
    > > )
    > > and props.propname = 'Location' and props.propvalue = 'London'
    > > )
    > > )
    > > and
    > > (
    > > exists (
    > > select *
    > > from props
    > > where props.ident in (
    > > select tree2.ident
    > > from tree tree2
    > > where tree1.hierarchy like tree2.hierarchy + '%'
    > > )
    > > and propname = 'Industry' and propvalue = 'Oil'
    > > )
    > > )
    > >
    > > Bye,
    > > Delbert Glass
    > >
    > > "Kevin Munro" <kevin_remove8839421c3amulet.com> wrote in message
    > > news:3f0ac89e$0$45176$65c69314mercury.nildram.net ...
    > > > I think I have it...
    > > >
    > > > select ident from
    > > > (
    > > > select t.ident from tree t join tree x on t.hierarchy like
    > x.hierarchy+'%'
    > > > where x.ident in
    > > > (select ident from props where propname='Industry' and
    propvalue='Oil')
    > > > UNION ALL
    > > > select t.ident from tree t join tree x on t.hierarchy like
    > x.hierarchy+'%'
    > > > where x.ident in
    > > > (select ident from props where propname='Location' and
    > > propvalue='London')
    > > > ) results group by ident having count(ident)=2
    > > >
    > > > Now to test on the big database :-)
    > > >
    > > > Kevin.
    > > >
    > > >
    > > > "Kevin Munro" <kevin_remove8839421c3amulet.com> wrote in message
    > > > news:3f0aa84a$0$45175$65c69314mercury.nildram.net ...
    > > > > Hello, I've a problem with hierarchies and relation division.
    > > > >
    > > > > I'd be really grateful for any help on this, I've included all
    > > > > my DDL below with comments to explain the steps.
    > > > >
    > > > > Hope this makes sense!
    > > > >
    > > > > Many thanks,
    > > > >
    > > > > Kevin Munro.
    > > > > [url]www.c3amulet.com[/url]
    > > > >
    > > > > -- Here's a fictitious example of a hierarchy and relational
    > > > > -- division problem. In my app I have more tables than this
    > > > > -- and the data is on a totally different subject but I've created
    > > > > -- these tables to hopefully illustrate my problem in as
    > > > > -- straightforward a way as possible.
    > > > >
    > > > > -- All my staff work in London and my contractors work in New York.
    > > > > -- I want a query that will return all my staff who are
    > > > > -- available in London and have oil industry experience.
    > > > > -- I can have many properties against each container -
    > > > > -- for example, the Staff root level container could
    > > > > -- be made up of Location, Salary, Type, Age Range etc...
    > > > > -- I know that these properties are entities themselves and should
    > > > > -- be represented in their own tables but what I am trying to do
    > > > > -- here is to build a generic searching tool over a hierarchy
    > > > > -- where the user can 'tag' any number of properties against a
    > > > > -- container. The user can define any of these property names
    > > > > -- and property values and they can already build up the hierarchy
    > > > > -- themselves. I have triggers and stored procedures to maintain
    > > > > -- the lvl and hierarchy columns.
    > > > > -- Also, in real life I would also have a nodes table - we call
    > > > > -- this containers and points in our company but let's just
    > > > > -- stick to one tree table for this example...
    > > > >
    > > > > drop table tree
    > > > > drop table props
    > > > >
    > > > > create table tree (
    > > > > ident int,
    > > > > name varchar(15),
    > > > > parent int,
    > > > > lvl int,
    > > > > hierarchy varchar(50)
    > > > > )
    > > > >
    > > > > create table props (
    > > > > ident int,
    > > > > propname varchar(15),
    > > > > propvalue varchar (15)
    > > > > )
    > > > >
    > > > > set nocount on
    > > > > -- set up tree structure
    > > > > delete from tree
    > > > > insert into tree values (100,'Staff',0,0,'.100.')
    > > > > insert into tree values (101,'Developers',100,1,'.100.101.')
    > > > > insert into tree values (102,'Web',101,2,'.100.101.102.')
    > > > > insert into tree values (103,'Desktop',101,2,'.100.101.103.')
    > > > > insert into tree values (150,'Managers',100,1,'.100.150.')
    > > > > insert into tree values (151,'Projects',150,2,'.100.150.151.')
    > > > > insert into tree values (200,'Contracters',0,0,'.200.')
    > > > > insert into tree values (201,'Developers',200,1,'.200.201.')
    > > > > insert into tree values (202,'Linux',201,2,'.200.201.202.')
    > > > > insert into tree values (203,'Web',202,2,'.200.201.203.')
    > > > >
    > > > > -- show the hierarchy
    > > > > SELECT te(' | ', lvl) + name as people
    > > > > from tree order by hierarchy
    > > > >
    > > > > -- this should give us a hierarchy of
    > > > > -- Staff
    > > > > -- | Developers
    > > > > -- | | Web
    > > > > -- | | Desktop
    > > > > -- | Managers
    > > > > -- | | Projects
    > > > > -- Contracters
    > > > > -- | Developers
    > > > > -- | | Linux
    > > > > -- | | Web
    > > > >
    > > > > -- insert the property data
    > > > > -- each container can have any number of properties not unique to
    them
    > > > > -- hence I can't express these properties as part of the hierarchy.
    > > > >
    > > > > -- this is a simplified property table - in real life I have
    > > > > -- further tables to normalise the data.
    > > > >
    > > > > delete from props
    > > > > insert into props values (100,'Location','London')
    > > > > insert into props values (200,'Location','New York')
    > > > > insert into props values (102,'Industry','Oil')
    > > > > insert into props values (103,'Industry','Oil')
    > > > > insert into props values (103,'Industry','Water')
    > > > >
    > > > > -- now some more essentially random data to make it a bit more real
    > > > > -- and ensure we don't get back false results
    > > > > insert into props values (100,'Salary','Grade A')
    > > > > insert into props values (102,'Skill','A')
    > > > > insert into props values (102,'Experience','C')
    > > > >
    > > > > -- So the props table links on a one to many relationship with
    > > > > -- the tree table. For any record in the tree table I can have
    > > > > -- any number of properties in the props table.
    > > > >
    > > > > -- e.g. My web developers in London have these properties...
    > > > >
    > > > > select * from props where ident=102
    > > > >
    > > > > -- ident propname propvalue
    > > > > -- ----------- -------------------- --------------------
    > > > > -- 102 Industry Oil
    > > > > -- 102 Skill A
    > > > > -- 102 Experience C
    > > > >
    > > > > -- but to get all my developers in London with Oil Industry
    > > > > -- experience I need to do some relational division.
    > > > >
    > > > > -- this query gets me all those containers that have either.
    > > > >
    > > > > select tree.ident,name,propname,propvalue from tree,props where
    > > > > tree.ident=props.ident and
    > > > > ((propname='Location' and propvalue='London') or
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > >
    > > > > -- gives me these results...
    > > > > -- ident name propname propvalue
    > > > > -- ----------- --------------- --------------- ---------------
    > > > > -- 100 Staff Location London
    > > > > -- 102 Web Industry Oil
    > > > > -- 103 Desktop Industry Oil
    > > > >
    > > > > -- and this query...
    > > > > select tree.ident,name,propname,propvalue from tree,props where
    > > > > tree.ident=props.ident and
    > > > > ((propname='Location' and propvalue='London') and
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > >
    > > > > -- gives me no results...
    > > > >
    > > > > -- if these properties were on the same level (such as
    > > > > -- developers with Oil industry experience and a Skill of A
    > > > > -- then I can do this using relational division...
    > > > >
    > > > > -- I'll do this in parts..
    > > > > -- Here are the properties I'm intersted in...
    > > > >
    > > > > select * from props where
    > > > > ((propname='Skill' and propvalue='A') or
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > >
    > > > > -- from looking at these results it's obvious I'm interested
    > > > > -- in ident 102 as this matches both criteria...
    > > > >
    > > > > -- ident propname propvalue
    > > > > -- ----------- --------------- ---------------
    > > > > -- 102 Industry Oil
    > > > > -- 103 Industry Oil
    > > > > -- 102 Skill A
    > > > >
    > > > > -- let's count the idents
    > > > > select count(ident) x, ident from props where
    > > > > ((propname='Skill' and propvalue='A') or
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > > group by ident
    > > > >
    > > > > -- x ident
    > > > > -- ----------- -----------
    > > > > -- 2 102
    > > > > -- 1 103
    > > > >
    > > > > -- ok, I'm intersted in x=2 so let's get the ident
    > > > > select ident from
    > > > > (select count(ident) x, ident from props where
    > > > > ((propname='Skill' and propvalue='A') or
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > > group by ident) results where x=2
    > > > >
    > > > > -- and I can get ident 102
    > > > > -- ident
    > > > > -- -----------
    > > > > -- 102
    > > > >
    > > > > -- So my problem is how on earth can I get this query to
    > > > > -- return ident 102
    > > > >
    > > > > select ident from
    > > > > (select count(ident) x, ident from props where
    > > > > ((propname='Location' and propvalue='London') or
    > > > > (propname='Industry' and propvalue='Oil'))
    > > > > group by ident) results where x=2
    > > > >
    > > > > -- returns no records because they are at different
    > > > > -- hierarchy levels
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Kevin Munro Guest

Similar Threads

  1. Clustering in the presence of hierarchies (fwd)
    By Ioannis Theoharis in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 11th, 03:27 PM
  2. Clustering in the presence of hierarchies
    By Ioannis Theoharis in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 10th, 03:15 AM
  3. Form Field Hierarchies
    By Amit_Arora@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 0
    Last Post: April 21st, 11:03 AM
  4. problems with division
    By greg brant in forum Macromedia Director Lingo
    Replies: 3
    Last Post: August 1st, 01:00 PM
  5. Hierarchies
    By Kevin Munro in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 8th, 10:19 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139