you can.
the easiest tho not the most accepted way to do this is to use a union
statement.
for example:
<cfquery datasource="#datasource#" name=bookInfo>
select 1 as displayOrder, books.bookName, books.ISBN, authors.fullname as
caption
from books left join booksToAuthors on books.bookID=booksToAuthors.bookID
left join authors on booksToAuthors.authorID=authors.authorID
where books.bookID=#val(bookID)#
union
select 1 as displayOrder, books.bookName, books.ISBN,
categories.categoryName
from books left join booksToCategories on
books.bookID=booksToCategories.bookID
left join categories on booksToCategories.categoryID=categories.categoryID
where books.bookID=#val(bookID)#
order by displayOrder, caption
</cfquery>
<cfoutput group=bookName query="bookInfo">
#bookName# - <br>
<cfoutput group=displayOrder>#caption#,</cfoutput>
<br>
</cfoutput>
</cfoutput>
the nice part about that script is that you can always add more
information to each section of the union to make it more section specific.
you could also do this by using a whole bunch of joins.. but hten your
recordset gets huge and you ahve to add a nested cfoutput for each new
thing you want to use...
-chris
On Thu, 26 Jul 2001, Hamid Hossain wrote:
> 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
