> 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
