Sorry I always forget to include the code- can't you guys read my mind :}
Here are the two queries I built because i couldn't figure out how to do a
four table join. (it was cobbled together from about 8 SQL books at
borders, and yes I did many google searches for table joins over 3 tables,
but couldn't find any.) I just can't seem to get extrapolate 4 tables from
any of the examples I�ve seen.
If anyone can figure out how to make these two queries one joined query i
would be in your debt...
<cfquery name="searchResults" datasource="#attributes.dsn#"
cachedwithin="#createTimeSpan(0,0,10,0)#">
SELECT *
FROM (author
INNER JOIN bookAuthor
ON author.authorId = bookAuthor.bookAuthor_authorId)
INNER JOIN book
ON book.bookId = bookAuthor.bookAuthor_bookId
WHERE (author.authorFirstName LIKE '%#form.searchTerms#%'
OR author.authorLastName LIKE '%#form.searchTerms#%'
OR book.bookTitle LIKE '%#form.searchTerms#%'
OR book.BookISBN LIKE '%#form.searchTerms#%')
ORDER BY book.bookTitle
</cfquery>
<cfif searchResults.recordcount NEQ 0>
<cfquery name="displayPicture" datasource="#attributes.dsn#">
SELECT *
FROM bookImage INNER JOIN book
ON bookImage.bookImage_bookId = book.BookId
WHERE bookImage_bookId IN (#valueList(searchResults.bookID)#)
ORDER BY book.bookTitle
</cfquery>
</cfif>
As always thank you so much
Jason
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
