SQL Server 2008 and matching of subsets

Question

I am working with SQL Server 2008 and I need to match sets of Parts. These Parts can contain other Parts (non recursive to make it easier).

Table Part

PartId ...
1
2
3
30
40
50
60
70

Table PartRelation

PartIdMother PartIdChild (non recursive)
1            30
1            40
1            50
1            60

2            30
2            40

3            30
3            40
3            50

Now I have a set of random Parts and I want to know which aggregated Part this might possibly be.

Table PartRandom

Id     PartId
1      30
2      40
3      70

Obviously the parts 1, 2 and 3 will match for the lines 1 and 2. The expected result of a query would be something like this:

Expected Result

Id     PartId  PartIdMother
1      30      1
2      40      1

1      30      2
2      40      2

1      30      3
2      40      3

I have a total blockade here. Please share your wisdom to utilize SQL 2008 to the max. Top notch would be the support of recursive relations in the Table PartRelation.

Here is the SQL code:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Part]') AND type in (N'U'))
DROP TABLE [dbo].[Part]
GO
CREATE TABLE [dbo].[Part]( [PartId] [int] NOT NULL,
CONSTRAINT [PK_Part] PRIMARY KEY CLUSTERED ([PartId] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO
INSERT INTO [Area51].[dbo].[Part] ([PartId]) VALUES (1)
INSERT INTO [Area51].[dbo].[Part] ([PartId]) VALUES (2)
INSERT INTO [Area51].[dbo].[Part] ([PartId]) VALUES (3)
INSERT INTO [Area51].[dbo].[Part] ([PartId]) VALUES (30)
INSERT INTO [Area51].[dbo].[Part] ([PartId]) VALUES (40)
INSERT INTO [Area51].[dbo].[Part] ([PartId]) VALUES (50)
INSERT INTO [Area51].[dbo].[Part] ([PartId]) VALUES (60)
INSERT INTO [Area51].[dbo].[Part] ([PartId]) VALUES (70)
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[PartRelation]') AND type in (N'U'))
DROP TABLE [dbo].[PartRelation]
GO
CREATE TABLE [dbo].[PartRelation]([PartIdMother] [int] NOT NULL,
[PartIdChild] [int] NOT NULL,
CONSTRAINT [PK_PartRelation] PRIMARY KEY CLUSTERED
( [PartIdMother] ASC, [PartIdChild] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO
INSERT INTO [Area51].[dbo].[PartRelation]([PartIdMother],[PartIdChild]) VALUES(1, 30)
INSERT INTO [Area51].[dbo].[PartRelation]([PartIdMother],[PartIdChild]) VALUES(1, 40)
INSERT INTO [Area51].[dbo].[PartRelation]([PartIdMother],[PartIdChild]) VALUES(1, 50)
INSERT INTO [Area51].[dbo].[PartRelation]([PartIdMother],[PartIdChild]) VALUES(1, 60)
INSERT INTO [Area51].[dbo].[PartRelation]([PartIdMother],[PartIdChild]) VALUES(2, 30)
INSERT INTO [Area51].[dbo].[PartRelation]([PartIdMother],[PartIdChild]) VALUES(2, 40)
INSERT INTO [Area51].[dbo].[PartRelation]([PartIdMother],[PartIdChild]) VALUES(3, 30)
INSERT INTO [Area51].[dbo].[PartRelation]([PartIdMother],[PartIdChild]) VALUES(3, 40)
INSERT INTO [Area51].[dbo].[PartRelation]([PartIdMother],[PartIdChild]) VALUES(3, 50)
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[PartRandom]') AND type in (N'U'))
DROP TABLE [dbo].[PartRandom]
GO
CREATE TABLE [dbo].[PartRandom](
[Id] [int] NOT NULL,
[PartId] [int] NULL,
CONSTRAINT [PK_PartRandom] PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO
INSERT INTO [Area51].[dbo].[PartRandom]([Id],[PartId]) VALUES (1, 30)
INSERT INTO [Area51].[dbo].[PartRandom]([Id],[PartId]) VALUES (2, 40)
INSERT INTO [Area51].[dbo].[PartRandom]([Id],[PartId]) VALUES (3, 70)

Answer 1

Try:

select ra.Id, ra.PartId, re.PartIdMother
from PartRandom ra
join PartRelation re on ra.PartId = re.PartIdChild

- to return parent parts where any of the child parts are in PartRandom.

EDIT: For a recursive version, try:

;with cte as 
(select PartIdMother PartIdAncestor, PartIdChild from PartRelation r
 where not exists (select null from PartRelation r1 where r1.PartIdChild = r.PartIdMother)
/* remove where not exists condition to select all intermediate levels */
 union all
 select c.PartIdAncestor, r.PartIdChild 
 from cte c
 join PartRelation r on c.PartIdChild = r.PartIdMother)
select ra.Id, ra.PartId, re.PartIdAncestor
from PartRandom ra
join cte re on ra.PartId = re.PartIdChild