SQL problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can any SQL expert figure this one out

I have a table with 4 columns: ID, Area, Keyword and Parent
The ID is an autonumber primary key and the Parent points to other ID's in the same table
The top=level keywords have a parent of NULL
Thus this table stores hierarchies of keywords

Now the complication, certain Keyword hierarchies are restricted to certain Areas oin which case Area is non-Null. Are is filled in onlt for the top level keywords i.e. Keywords with parent of Null

Now my problem is to list all Keywords where the top level keyword has a certain Area
Here is an exampl
ID Area Keyword Paren
1 USA Ca
2 USA Truc
3 China Ricksha
4 Buick
5 Dodge
6 Electric
7 Pedaled
8 Ram
9 12V

If Area is set to USA, it should return ID's 1,2,4,5,
If Area is set to China, it should return ID's 3,6,7,9
 
Here is one solution based upon 3 levels of hierarchy

SELECT * FROM Keyword T1
WHERE [Id] IN
(SELECT T2.[Id] FROM Keyword T2 WHERE T2.area = [Enter Area])
UNION
SELECT * FROM Keyword T1
WHERE [Id] IN
(SELECT T4.[Id] FROM Keyword T3 INNER JOIN Keyword T4 ON
T3.[Id] = T4.parent WHERE T3.area = [Enter Area])
UNION
SELECT * FROM Keyword T1
WHERE [Id] IN
(SELECT T7.[Id] FROM (Keyword T5 INNER JOIN Keyword T6 ON
T5.[Id] = T6.parent) INNER JOIN Keyword T7 ON T6.[Id] =
T7.parent WHERE T5.area = [Enter Area])

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Can any SQL expert figure this one out?

I have a table with 4 columns: ID, Area, Keyword and Parent.
The ID is an autonumber primary key and the Parent points
Click to expand...
to other ID's in the same table.
The top=level keywords have a parent of NULL.
Thus this table stores hierarchies of keywords.

Now the complication, certain Keyword hierarchies are
Click to expand...
restricted to certain Areas oin which case Area is
non-Null. Are is filled in onlt for the top level keywords
i.e. Keywords with parent of Null.
 
Back
Top