Many-to-Many Self Joins ?

Hi Folks,
I need to implement a many-to-many self join for a table of acronyms ie each
acronym may _reference_ one or more other acronyms and, in addition, each
acronym may _be referenced by_ one or more other acronyms. I tried to
utilize the code in
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=medium"
see DDL and data below . However the results I got from the query don't
appear correct. Can you see where I've gone wrong?
Thanks, Simon
CREATE TABLE [Acronyms] (
[Acronym_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Acronym] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_Acronyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Acronym_ID Acronym Description
1 ABC ABC
2 DEF DEF
3 XYZ XYZ
CREATE TABLE [Synonyms] (
[Acronym_ID] [int] NOT NULL ,
[Synonym_ID] [int] NOT NULL ,
[Start_Date] [datetime] NOT NULL ,
[End_Date] [datetime] NULL ,
CONSTRAINT [PK_Synonyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID],
[Synonym_ID],
[Start_Date]
) ON [PRIMARY] ,
CONSTRAINT [FK_Synonyms_Acronyms] FOREIGN KEY
(
[Acronym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
),
CONSTRAINT [FK_Synonyms_Acronyms1] FOREIGN KEY
(
[Synonym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
)
) ON [PRIMARY]
GO
Acronym_ID Synonym_ID Start_Date End_Date
2 1 01-Jan-05
3 1 06-Jun-05
1 2 02-Feb-05
1 3 02-Feb-05
SELECT TOP 100 PERCENT A.Acronym AS Acronym, R.Acronym AS Synonym
FROM dbo.Acronyms A LEFT OUTER JOIN
dbo.Synonyms S INNER JOIN
dbo.Acronyms R ON S.Acronym_ID = R.Acronym_ID ON
A.Acronym_ID = S.Acronym_ID
ORDER BY A.Acronym
Results...
Acronym Synonym
ABC ABC
ABC ABC
DEF DEF
XYZ XYZHi Simon,
What's your expected result? Could you clarify more? In the following
article, the author will show a simple example that traverses a hierarchy
and displays the output in indented format.
Working with hierarchical data in SQL Server databases
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
BTW, In SQL Server 2005, we support Common Table Expression support
recursive directly.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
I don't believe my problem is simply hierarchical given that an entity can
have more than one parent
Simon
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:af9Cmm26FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Simon,
> What's your expected result? Could you clarify more? In the following
> article, the author will show a simple example that traverses a hierarchy
> and displays the output in indented format.
> Working with hierarchical data in SQL Server databases
> http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
> BTW, In SQL Server 2005, we support Common Table Expression support
> recursive directly.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Simon,
I cannot see the image in link
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=m
edium", would you please clarify the structure of your samples more?
What's your expected result?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.