MS SQL Server - Table Hierarchy is SQL Server 2000

Author

Table Hierarchy is SQL Server 2000

Ganesh Muthuvelu

2006-02-03, 8:23 pm

Hello,
If in a database, "table3" depends on "table2" and "table2" depends on
"table1" - how would I write a query (on the system tables) which will take
"table3" as a parameter and return me this result as below in a hierarchy?

Name Parent Table Name
table3 table2
table2 table1

Thanks,
Ganesh

John Bell

2006-02-04, 11:23 am

Hi

I presume your are talking about foreign key relationships!

For example the example PUBS database has the pubs table that is reference
to pub_id from the authors table which in turn is reference by the
titleauthors table (on au_id) and also references the title table (on
title_id) .

If you want to list publisher name, author name, and title for each book you
would need to join the tables together:

SELECT p.pub_name, a.au_lname + au_fname AS Author, t.title
FROM Publishers p
JOIN Authors a ON a.pub_id = p.pub_id
JOIN TitleAuthor x ON A.au_id = x.au_id
JOIN Titles t ON t.title_id = x.title_id

Also see the topics "Table Relationships", "Join Fundamentals" and "Using
Joins" in Books Online.

You may also want to read up on "SQL Database Normalization Rules" and
"Normalization" in the Creating and Managing Databases section

John

"Ganesh Muthuvelu" < GaneshMuthuvelu@disc
ussions.microsoft.com> wrote in
message news:24C63601-EF6A-4DB9-9030- F6DF783643E6@microso
ft.com...
> Hello,
> If in a database, "table3" depends on "table2" and "table2" depends on
> "table1" - how would I write a query (on the system tables) which will
> take
> "table3" as a parameter and return me this result as below in a hierarchy?
>
> Name Parent Table Name
> table3 table2
> table2 table1
>
> Thanks,
> Ganesh

Sponsored Links