Dave's right, you need a join to tblModel. Generally when you return some
incredible amount of rows that you are not expecting from a query that is
joining tables its because you are missing a join or two.
One thing you might consider is using a View for this. Then all you would
have to do is send preID into the view to get what you need. Views are
cached virtual tables, they allow for abstraction, but they are really
convenient when it comes to programming in that you can do this big work
horse query of yours as a view database side and then your code would be a
simple
SELECT WHATEVER
FROM V_CARS
WHERE preID IN (#preID# )
This way if you ever change the logic of your tables, you only have to
change the view and not the live code. I hate to type, so the less I do,
the better is how I figure it.
If you ever program in java, think of a view as a class that has
incapsulated data and your little query above as one of its methods. You
can do whatever you want to the tables as long as the things you want to
query remain the same.
On the abstraction side of things, if someone ever sees your code thru one
of those .htr or ::data things they can DELETE * from V_CARS all they want
and they go hungry a bow bow bow.
At the rate business logic seems to change on the web, this kind of stuff
makes programming the client tier a lot easier.
----- Original Message -----
From: "David Baskin" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Sunday, February 04, 2001 1:27 PM
Subject: 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.
>
> david
>
>
>
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
