So you're searching in the title and description columns of a products
table?
Is the GROUP BY needed? I'm not sure it is at this stage.
SELECT p.Code
FROM tblProducts p
WHERE p.Title LIKE '#FORM.search#'
OR p.Description LIKE '#FORM.search#'
If the above(shortened version of your's below) works as you want it to, try
this for the images.
SELECT
p.Code,
(
SELECT TOP 1 ImageName
FROM tblImages i WHERE i.ImageID = p.ImageID
) 'ImageName'
FROM tblPMCodes p
WHERE p.PMTitle LIKE '#FORM.search#'
OR p.PMDescription LIKE '#FORM.search#'
That should pull back one image for each product.
How does that work out?
Ade
-----Original Message-----
From: Will Tomlinson [mailto:[EMAIL PROTECTED]
Sent: 23 January 2005 01:17
To: CF-Talk
Subject: Re: How to make this query work
>Are you saying the search works with or without the join to the images
>table?
Ok. I have this working perfectly... minus the images.
SELECT tblprodmodelcodes.prodmodelcode,
tblprodmodelcodes.prodmodeltitle,tblprodmodelcodes.prodcategory,
tblprodmodelcodes.prodsellprice
FROM tblprodmodelcodes
WHERE tblprodmodelcodes.prodmodeltitle LIKE '%#FORM.search#%'
OR tblprodmodelcodes.prodmodeldescription LIKE '%#FORM.search#%'
GROUP BY tblprodmodelcodes.prodmodelcode,
tblprodmodelcodes.prodmodeltitle,tblprodmodelcodes.prodcategory,
tblprodmodelcodes.prodsellprice
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware: a new and convenient web-based time tracking application. Start
tracking and documenting hours spent on a project or with a client with Logware
today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191464
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
