Selecting From 5+ tables in one query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting From 5+ tables in one query

Author

Topic

gravenine
Starting Member

1 Post

Posted - 2002-06-25 : 18:29:15

I'm trying to select from about 12 tables, and I have bridge tables all over.

Here is what I have:

Tables: (i'll only list a few)
-----------------------------------
book_info (book info table, many book statistics)
book_artist (artist_id, book_id) bridge Table
book_colorist (colorist_id, book_id) bridge Table
colorist (colorist_name, colorist_id)
artist (artist_name, artist_id)

The book can have multipile artists, colorists and so forth.
So I needed some bridge tables such as: book_artist, book_colorist.
The only values that these tables hold are: book_id & colorist_id. So that we can have multiple associations to one book. The same goes for the artist table.

Now when performing a select, I want to return the info from the book_info table and the book_colorist, and book_artist tables. I'm basing everything off the book_id (primary key in book_info).

So if I want to select the data from the colorist table, how am I going to get to that data? I know how to do joins, and I have got this far (see belo), but I cannot do anything from here, i'm stuck.

Any help would be apprciated.

Here is the code:

select bi.title, bi.issue_numb, bi.release_date, bi.dedication, bi.synopsis, bi.book_id, bi.indecia from book_info bi
INNER JOIN book_colorist bc 
	ON bi.book_id = bc.book_id	
where bi.book_id = '1-1-13-0'

MakeYourDaddyProud

184 Posts

Posted - 2002-06-25 : 19:10:46

Perhaps I am overlooking your problem, why can't you just add an extra inner join line thus

select co.*, bi.title, bi.issue_numb, bi.release_date, bi.dedication, bi.synopsis, bi.book_id, bi.indecia

from book_info bi

INNER JOIN book_colorist bc
ON bi.book_id = bc.book_id

INNER JOIN colorist co
ON co.colorist_id = bc.colorist_id

where bi.book_id = '1-1-13-0'

HTH

Danno

<<monet makes money>>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources