You forgot to remove the select *! Take a look at my version - you need to select *only* ITEM_NUM in your sub-query :) You _could_ rewrite it using EXISTS, but I find the IN statement to be much easier to follow, as the EXISTS syntax gets really messy, really quickly. I only use EXISTS when it will benefit in terms of execution time, and in your case, I don't think it will.
Roland -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Les Mizzell Sent: Friday, April 15, 2005 7:52 PM To: [email protected] Subject: Re: [CFCDev] One more query question I get the idea, but... "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." Still playing with it..... Roland Collins wrote: > You might get better performance if you nest the first query. That way > there's only one round-trip to the DB and the server can optimize your > query. Also, you'll want to use CFQUERYPARAM to avoid SQL injection > attacks. As-is, it would be very easy to mess around with your database. > > <cfquery name="srch2" datasource="myDATA"> > SELECT > color.colorCAT, > color.colorNAME, > color.colorID, > colorLOOKUP.itemNUM, > colorLOOKUP.colorID, > papers.ITEM_NUM, > papers.ID, > papers.PA_TYPE, > papers.PA_CATEGORY > FROM ( color > INNER JOIN colorLOOKUP ON color.colorID = colorLOOKUP.colorID) > INNER JOIN papers ON colorLOOKUP.itemNUM = papers.ITEM_NUM > WHERE papers.ITEM_NUM NOT IN (SELECT ITEM_NUM FROM papers > WHERE > (ITEM_NAME LIKE > '%#FORM.SRCH#%' or > PA_TYPE LIKE '%#FORM.SRCH#%' > or > PA_CATEGORY LIKE > '%#FORM.SRCH#%' or > PA_COUNTRY LIKE > '%#FORM.SRCH#%' or > ITEM_DESC LIKE '#FORM.SRCH# > %' )) > AND (color.colorCAT = '#FORM.SRCH#' > OR color.colorNAME = '#FORM.SRCH#' ) > </cfquery> > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Les Mizzell > Sent: Friday, April 15, 2005 6:51 PM > To: [email protected] > Subject: Re: [CFCDev] One more query question > > Here's teh whole thing. Had a mistake in the previous: > > > <cfquery name="srch" datasource="myDATA"> > SELECT * FROM papers > Where > (ITEM_NAME LIKE '%#FORM.SRCH#%' or > PA_TYPE LIKE '%#FORM.SRCH#%' or > PA_CATEGORY LIKE '%#FORM.SRCH#%' or > PA_COUNTRY LIKE '%#FORM.SRCH#%' or > ITEM_DESC LIKE '#FORM.SRCH# %' ) > </cfquery> > > <cfquery name="srch2" datasource="myDATA"> > SELECT > color.colorCAT, > color.colorNAME, > color.colorID, > colorLOOKUP.itemNUM, > colorLOOKUP.colorID, > papers.ITEM_NUM, > papers.ID, > papers.PA_TYPE, > papers.PA_CATEGORY > FROM ( color > INNER JOIN colorLOOKUP ON color.colorID = colorLOOKUP.colorID) > INNER JOIN papers ON colorLOOKUP.itemNUM = papers.ITEM_NUM > WHERE papers.ITEM_NUM NOT IN (#quotedValueList(srch.ITEM_NUM)#) > AND (color.colorCAT = '#FORM.SRCH#' > OR color.colorNAME = '#FORM.SRCH#' ) > </cfquery> > > Working fine! > > Now if I can figure out how to *combine* the results of query1 and > query1 into one output statement I'll have it licked! > -- ----------- Les Mizzell ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). An archive of the CFCDev list is available at www.mail-archive.com/[email protected] ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). An archive of the CFCDev list is available at www.mail-archive.com/[email protected]
