Select Books.*
>From Books,
BookAuthors,
BookCategories,
BookLanugage,
Authors,
Categories,
Languages
Where Books.BookID = BookAuthors.BookID
AND BookAuthors.AuthorID = Authors.AuthorID
AND Books.BookID = BookCategories.BookID
AND BookCategories.CategoryID = Categories.CategoryID
AND Book.BookID = BookLanguages.BookID
AND BookLanguages.LanguageID = Languages.LanguageID
This will do what you want. However, it will only show books that have a
Author, Category, AND a language. A book with only an author wouldn't show
up.
To get around that problem, you would need to use LEFT OUTER JOINS on the
tables in the where clause.
Hope that helps.
Shawn Grover
-----Original Message-----
From: Hamid Hossain [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 26, 2001 3:39 PM
To: CF-Talk
Subject: RE: come on Forta !
Come on Forta! I knew that should be a linker table
for all many to many relations. I tried to shorten the
chart.
I am a good student who read most of your books, Ben.
still need the help:
Books --- BookAuthors --- Authors
|
|- BookCategories --- Categories
|
|- BookLanguages --- Languages
As you can see, Three tables are linked to (Books)
table in many to many relations. I want to output a
list of all books with all (Authors), (Categories)
and (Languages) of each single book.
Could you please help me with the best way doing
that. In another way, Can I do it with a single
query?!.
Regards,
Hamid Hossain
--- Ben Forta <[EMAIL PROTECTED]> wrote:
> Not the ideal design, change it to this:
>
> Books --- BookAuthors --- Authors
> |
> |- BookCategories --- Categories
> |
> |- BookLanguages --- Languages
>
> All relationships are one to many (one on the left
> and right, many in the
> center). Will make your life much easier.
>
> --- Ben
>
>
> -----Original Message-----
> From: Hamid Hossain [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, July 26, 2001 11:52 AM
> To: CF-Talk
> Subject: come on Forta !
>
>
> Hi folks,
>
> I'v just ordered Ben Forta's new book. I am
> woundering
> if I will find a way to solve this problem. I have
> the
> following database tables:
>
>
> ------- many -- Authors
> | (Table)
> |
> |
> |
> Books - many --------- many -- Categories
> (Table) | (Table)
> |
> |
> |
> ------- many -- Languages
> (Table)
>
>
> As you can see, Three tables are linked to (Books)
> table in many to many relations. I want to output a
> list of all books with all (Authors), (Categories)
> and
> (Languages) of each single book.
>
> Could you please help me with the best way doing
> that.
> In another way, Can I do it with a single query?!.
>
> Any CF code will be appreciated.
>
> Regards,
> Hamid Hossain
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
