Re: Hierarchic tables in Queries ??
From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 07/13/04
- Next message: dario d.meroni_at_gmde.it: "UPDATETEXT between two servers"
- Previous message: damien: "6.5 Random List"
- In reply to: Claude Vernier: "Hierarchic tables in Queries ??"
- Next in thread: Claude Vernier: "Re: Hierarchic tables in Queries ??"
- Reply: Claude Vernier: "Re: Hierarchic tables in Queries ??"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 13 Jul 2004 10:20:45 +0100
Typically a hierarchy contains all the items in the tree with a nullable,
self-referencing foreign key. Something like this:
CREATE TABLE SomeTable (parent INTEGER NULL REFERENCES SomeTable (id), id
INTEGER NOT NULL PRIMARY KEY, name_e VARCHAR(10) NOT NULL, name_f
VARCHAR(10) NOT NULL)
INSERT INTO SomeTable (parent, id, name_e, name_f)
SELECT 1024, 1, 'Groceries', '...' UNION ALL
SELECT 1024, 2, 'Tools', '...' UNION ALL
SELECT 1, 3, 'Meat', '...' UNION ALL
SELECT 1024, 4, 'Car', '...' UNION ALL
SELECT NULL, 1024, 'Foo', '...' -- Missing from your table
Unless you add the missing 1024 value you can't use a foreign key to
guarantee the referential integrity of your hierarchy.
> Most important, I'm looking for a query that would return me
> all the first level THEME (ie: meaning they have 1024 as PARENT) but only
those
> that have an item below at any level.
Try this:
SELECT parent, id, name_e, name_f
FROM SomeTable AS S
WHERE parent = 1024
AND EXISTS
(SELECT *
FROM SomeTable
WHERE parent = S.id)
-- David Portas SQL Server MVP --
- Next message: dario d.meroni_at_gmde.it: "UPDATETEXT between two servers"
- Previous message: damien: "6.5 Random List"
- In reply to: Claude Vernier: "Hierarchic tables in Queries ??"
- Next in thread: Claude Vernier: "Re: Hierarchic tables in Queries ??"
- Reply: Claude Vernier: "Re: Hierarchic tables in Queries ??"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
