Prev: RDLC 2008 Error in print layout mode
Next: Synonyms
From: Rafeeque on 18 Jun 2010 05:18
Please look at this article, using WITH cluase you can have the recursive call inside the sql.

http://www.techiescraft.com/cte-(common-table-expression)-?-use-recursively_Article13.aspx




Fred wrote:

Find the parent of a set of children
04-Sep-08

Hello,

I need to write a query for the following requirements :
A table stores a 1-n relation ship (parent id, child id)
Another table stores some children ids.
I would like to get the parent having exactly the children stored in the
second table.
If no parent matches, then the query should return no row.

With these commands, the result I want is 2
As TEST contains 1 and 2 and REL contains (2,1) and (2,2)

CREATE TABLE [dbo].[REL](
[parent] [int] NOT NULL,
[child] [int] NOT NULL,
CONSTRAINT [PK_REL] PRIMARY KEY CLUSTERED
([parent] ASC,[child] ASC)) ON [PRIMARY]
CREATE TABLE [dbo].[TEST](
[child] [int] NOT NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
([child] ASC)) ON [PRIMARY]
INSERT dbo.REL VALUES(1,1)
INSERT dbo.REL VALUES(1,2)
INSERT dbo.REL VALUES(1,3)
INSERT dbo.REL VALUES(2,1)
INSERT dbo.REL VALUES(2,2)
INSERT dbo.REL VALUES(3,1)
INSERT dbo.REL VALUES(3,4)
INSERT dbo.TEST VALUES(1)
INSERT dbo.TEST VALUES(2)

Can you help me ?

Thanks.

--
Fred
foleide(a)free.fr

Previous Posts In This Thread:

On Thursday, September 04, 2008 11:06 AM
Fred wrote:

Find the parent of a set of children
Hello,

I need to write a query for the following requirements :
A table stores a 1-n relation ship (parent id, child id)
Another table stores some children ids.
I would like to get the parent having exactly the children stored in the
second table.
If no parent matches, then the query should return no row.

With these commands, the result I want is 2
As TEST contains 1 and 2 and REL contains (2,1) and (2,2)

CREATE TABLE [dbo].[REL](
[parent] [int] NOT NULL,
[child] [int] NOT NULL,
CONSTRAINT [PK_REL] PRIMARY KEY CLUSTERED
([parent] ASC,[child] ASC)) ON [PRIMARY]
CREATE TABLE [dbo].[TEST](
[child] [int] NOT NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
([child] ASC)) ON [PRIMARY]
INSERT dbo.REL VALUES(1,1)
INSERT dbo.REL VALUES(1,2)
INSERT dbo.REL VALUES(1,3)
INSERT dbo.REL VALUES(2,1)
INSERT dbo.REL VALUES(2,2)
INSERT dbo.REL VALUES(3,1)
INSERT dbo.REL VALUES(3,4)
INSERT dbo.TEST VALUES(1)
INSERT dbo.TEST VALUES(2)

Can you help me ?

Thanks.

--
Fred
foleide(a)free.fr

On Thursday, September 04, 2008 11:54 AM
Tom Cooper wrote:

SELECT x.parentFROM (SELECT r.parent, COUNT(*) AS Number FROM dbo.
SELECT x.parent
FROM (SELECT r.parent, COUNT(*) AS Number
FROM dbo.REL r
GROUP BY r.parent) AS x
WHERE x.Number = (SELECT COUNT(*) FROM dbo.TEST t)
AND NOT EXISTS (SELECT *
FROM dbo.REL r2
WHERE x.parent = r2.parent AND r2.child NOT IN (SELECT t2.child FROM
dbo.TEST t2));

Tom

"Fred" <foleide(a)free.fr.invalid> wrote in message
news:u1iYE$pDJHA.1008(a)TK2MSFTNGP02.phx.gbl...

On Thursday, September 04, 2008 12:56 PM
Tom Cooper wrote:

Re: Find the parent of a set of children
<sterillo(a)hotmail.com> wrote in message
news:7db522e0-18dd-4bf5-808b-03e6da1a49d1(a)z6g2000pre.googlegroups.com...

<snip>


That works with the data the OP provided (if you change the t.id to
t.child). However, if you add an additional row to dbo.REL, e.g.,
INSERT dbo.REL VALUES(4,2)
This query will return parent 4. I believe this is not what the OP wanted
since the OP wrote "I would like to get the parent having exactly the
children stored in the second table.".

Tom

On Thursday, September 04, 2008 3:15 PM
Fred wrote:

Re: Find the parent of a set of children
<sterillo(a)hotmail.com> a ?crit dans le message de
news:76db910a-3c03-4425-ac46-63c3d17b25de(a)z6g2000pre.googlegroups.com...
On Sep 4, 12:56 pm, "Tom Cooper"
<tomcoo...(a)comcast.no.spam.please.net> wrote:


Thanks Tom and Terillo,
I will try this tomowow morning and give you feedback (I didn't install
SQL Server on this computer yet)
And yes, Tom understood what I meant and was not so clear as I reread
it. I don't want the query to return 4 is there is a (4, 2) couple in
the REL table. I do what I can with my poor english knowledge :-)

--
Fred
foleide(a)free.fr

On Thursday, September 04, 2008 3:25 PM
Fred wrote:

Re: Find the parent of a set of children
"--CELKO--" <jcelko212(a)earthlink.net> a ?crit dans le message de
news:e3126b76-0344-42e9-b486-37e221281ed5(a)z11g2000prl.googlegroups.com...


Hello,

Thanks for your advice. I understand that my words are not so correct.
Actually, it's not a tree, though "parent" and "child" can make you
think about it. I just gave very simplified DDL of my problem and use
words I know !
I am adapting this model :
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
(REL is LABEL_MARKING and TEST is a table obtained by splitting a label
string into markings elements)
If you have some advice about it, I am very happy to read you again.


--
Fred
foleide(a)free.fr

On Friday, September 05, 2008 1:46 AM
Fred wrote:

Re: Find the parent of a set of children
Fred ?crivait :


it is OK
Thanks again !

--
Fred
foleide(a)free.fr

On Saturday, September 06, 2008 2:35 PM
sterill wrote:

Re: Find the parent of a set of children
On Sep 4, 11:54=A0am, "Tom Cooper"
<tomcoo...(a)comcast.no.spam.please.net> wrote:
FROM
e

Try the following:

select r.Parent
from (select parent, count(*) as ChildCount
from rel r
group by parent) as r
join
(select parent, count(*) ChildCount
from rel r
join test t
on r.child =3D t.id
group by parent) x
on r.parent =3D x.parent
and r.childcount =3D x.childcount

Hope this helps

Sal Terillo

On Saturday, September 06, 2008 2:35 PM
--CELKO-- wrote:

So children and parents are two different kinds of entities and thisis not a
So children and parents are two different kinds of entities and this
is not a general tree? But that is not what your data shows. You
also have no constraint to prevent a child from being his own
ancestor. In fact, you require that the (1,1) node have exactly that
property, in violation of the definition of a tree. Did you mean
IMMEDIATE children or all descendants at any level? (imagine a "bush"
rooted at the parent versus a "chain" rooted at the parent).

I would use the Nested Sets model instead of an Adjacency List model,
if this is meant to be a tree.

On Saturday, September 06, 2008 2:35 PM
sterill wrote:

Re: Find the parent of a set of children
On Sep 4, 12:56=A0pm, "Tom Cooper"
<tomcoo...(a)comcast.no.spam.please.net> wrote:
ted

I understood the requirement to be that any parent whose full set of
children are contained in test should be returned. I've reread it and
while it's a little ambiguous I think you're probably correct.

Thanks for the feedback.

On Wednesday, March 18, 2009 3:24 AM
brad brad wrote:

Parent-children relation list sharepoint
If you do not want get your hands dirty, then take a look at

www.sharepointboost.com

where you can see Sharepoint Cascaded Lookup. This webpart is definitely what you are looking for.
In contrast, if you want reel your sleeves and do it by yourself. Then I recommend you take a look microsoft forum. Maybe there are some guys get some clues.


Submitted via EggHeadCafe - Software Developer Portal of Choice
MSChart For VB.Net
http://www.eggheadcafe.com/tutorials/aspnet/45729dc8-c102-4dc6-9aa7-4f6246763650/mschart-for-vbnet.aspx
 | 
Pages: 1
Prev: RDLC 2008 Error in print layout mode
Next: Synonyms