Re: [CFCDev] One more query question

[Les Mizzell]

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: CFCDev@cfczone.org
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 
cfcdev@cfczone.org 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/cfcdev@cfczone.org