Jason,
first, you should alias the tables and select real column names (get rid of
the asterick). Second, start with what you are after (probably the book in
this case)
Third, adjust the kinds of joins (inner outer full) - I've left those out
intentionally. Fourth, use query analyzer or some other non-CF tool to get
this query right before you try it in CF. It will save you some headaches
and the error information may be more useful.
-mk
SELECT BA.authorID, A.Author, BI.BookImage_id, B.book
FROM Book B JOIN BookImage BI
ON B.Bookid = BI.Bookid
JOIN bookAuthor BA
ON B.bookid = BA.bookAuthor_bookid
JOIN Author A
ON BA.bookAuthor_authorid = A.Authorid
WHERE (A.authorFirstName LIKE '%#form.searchTerms#%'
OR A.authorLastName LIKE '%#form.searchTerms#%'
OR B.bookTitle LIKE '%#form.searchTerms#%'
OR B.BookISBN LIKE '%#form.searchTerms#%')
ORDER BY B.bookTitle
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
Get the mailserver that powers this list at http://www.coolfusion.com
