Jochem, how about sub selects in the main select list? Would this be more
intensive than the aggregate function way? From the looks of it, I'd guess
it would do two extra queries per row of tblModelStuff. Is this better or
worse than your way? With this you do get more control over what image you
return, if the ID denotes which image comes first MAX()/MIN() will work in
the sub select, or there may be some other flag in the image table to use.
Thoughts anyone?
SELECT
ModelStuff,
MoreModelStuff,
YetMOREModelStuff,
(
SELECT TOP 1 ImageID
FROM tblImages
WHERE ImageID = m.ModelStuffID
) 'ImageID',
(
SELECT TOP 1 ImageName
FROM tblImages
WHERE ImageID = m.ModelStuffID
) 'ImageID'
FROM tblModelStuff m
WHERE .....
Ade
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: 22 January 2005 23:54
To: CF-Talk
Subject: Re: How to make this query work
Will Tomlinson wrote:
> I'm trying to run a search query on two tables. tblproductimages is a many
table with a FK- prodmodelcode dropped from tblprodmodelcodes. So there can
more than one image for a given model. How can I pare down the images to one
image per model?
Does it matter which image? If not:
SELECT
code.prodmodelcode,
code.prodmodeltitle,
code.prodsellprice,
MAX(image.imagename)
FROM
tblprodmodelcodes code INNER JOIN tblproductimages image
ON code.prodmodelcode = image.prodmodelcode
WHERE
tblprodmodelcodes.prodmodeltitle LIKE '%#FORM.search#%'
OR
tblprodmodelcodes.prodmodeldescription LIKE '%#FORM.search#%'
GROUP BY
code.prodmodelcode,
code.prodmodeltitle,
code.prodsellprice
Jochem
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 21/01/2005
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:191456
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
