I'd be willing to bet Hamid meant that:
For each 0 or 1 book, 0 or Many Authors
For each 0 or 1 book, 0 or Many Categories
For each 0 or 1 book, 0 or Many Languages
The problem with his scheme is that as soon as many authors or many
categories or many languages, you get a Cartesian Join of sorts:
3 Authors * 3 Categories * 3 Languages = 27 Book entries.
With a union, you can avoid having 9 entries and end up with 9. That seems
to be the only way to output each book -> parameter relationship.
SELECT BookName, Author AS Property, 'Authors' As PropertyType
FROM tblBooks LEFT OUTER JOIN tblAuthors ON
tblBooks.BookID = tblAuthors.BookID
UNION
SELECT BookName, Category AS Property, 'Categories' As PropertyType
FROM tblBooks LEFT OUTER JOIN tblCategries ON
tblBooks.BookID = tblCategries.BookID
UNION
SELECT BookName, Languages AS Property, 'Languages' As PropertyType
FROM tblBooks LEFT OUTER JOIN tblLanguages ON
tblBooks.BookID = tblLanguages.BookID
This will run slow as heck. Instead, I strongly suggest using cfquery three
times if you don't need to derive this table and make it relational.
-----Original Message-----
From: Hamid Hossain [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 26, 2001 2: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
