Also, doing the output on the query inside the new query is kinda odd... you
might look at using ValueList()... like
WHERE PreID IN (#ValueList(getUsedIDs.preID)#,0,00)
(the "0,00" on the end is there based on your query looking for those
values - nothing to do with the ValueList() call)
--Scott
----- Original Message -----
From: "Dave Watts" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Sunday, February 04, 2001 2:34 PM
Subject: RE: SQL trouble
> > Hello all, i'm having trouble with a query and i was hoping
> > someone could help me find the solution to my problem. I run
> > a query to get the ids of the objects a customer has stored.
> > in this case it's two ids. then i run another query that gets
> > all the details of these items and displays it. i run the
> > following query to get the details:
> >
> > <CFQUERY DATASOURCE="#myDataSource#" NAME="getDetails">
> > SELECT p.Year
> > , p.Series
> > , p.stockNum
> > // more and more columns...
> > FROM tblPreOwned p, tblMake m, tblWarranty w, tblModel mo
> > WHERE p.vehicleMakeID = m.makeID AND p.warrantyID =
> > w.wID AND (preID=0 OR
> > <CFOUTPUT QUERY="getUsedIDs">
> > p.preID=#preID# OR
> > </CFOUTPUT>
> > p.preID = 00)
> > </CFQUERY>
> >
> > what happens though is that i end up getting back over 1300
> > records for the details query. it returns the same two objects
> > over and over again. i'm not sure why this is happening and
> > unfortunately my SQL skills are not the best. If anyone can
> > tell me how to better structure this query to return the
> > proper results i would greatly appreciate it. TIA.
>
> Well, at first glance, it looks like you're using four tables, but you're
> not joining all four tables. This will result in a cross join or Cartesian
> product, in which every row from one table is matched against every row
from
> the other. I don't see a join condition for "tblModel mo".
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
> voice: (202) 797-5496
> fax: (202) 797-5444
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
