All you need to do is change the '=' to '*=' where applicable and you should
be fine. As far as ansi 88 syntax is concerned, I use it all the time. So
much cleaner then the new-fangled tripe microsoft pushes.
Good luck,
Alexander Sicular
Chief Technology Architect
Neurological Institute of New York
Columbia University
as867 [at] columbia {dot} edu
|-----Original Message-----
|From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
|Sent: Friday, July 27, 2001 1:53 PM
|To: CF-Talk
|Subject: RE: come on Forta !
|
|
|Woah!
|Ansi 88 Syntax!
|Haven't seen that since Btrieve!
|Are you even allowed to use that in most current databases?
|By the way, your joins are all inner joins. Won't work for this guy.
|
|-----Original Message-----
|From: Shawn Grover [mailto:[EMAIL PROTECTED]]
|Sent: Thursday, July 26, 2001 4:38 PM
|To: CF-Talk
|Subject: RE: come on Forta !
|
|
|
|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