Did you try SELECT dbr.PrimaryID AS PrimaryID, vr.Score AS Score ... Pascal
> -----Original Message----- > From: George Abraham [mailto:[EMAIL PROTECTED] > Sent: 03 May 2005 16:34 > To: CF-Talk > Subject: Re: Combining verity results with database query > > I toyed around with this code for some time. It is almost as if it > does not see that second table (or resultset) which is being joined. > For example, if this is the code: > > SELECT PrimaryID, Score > FROM dbResults dbr > INNER JOIN verityResults vr ON vr.key = dbr.PrimaryID > ORDER BY vr.Score > > then it throws an error that says Score does not exist in dbResults. > Well, no it does not, that is why I am joining on verityResults which > is where it exists. Here is how the tables look. > > dbResults > Column1: PrimaryID > Column2: Data2 > Column3: Data3 > Column4: Data4 > > verityResults > Column1: Key > Column2: Score > Column3: Body > and so on. > > > This code works: > SELECT * > FROM dbResults dbr > INNER JOIN verityResults vr ON vr.key = dbr.PrimaryID > ORDER BY vr.Score > > But then it shows me only the columns from dbResults. If I reverse the > order of the join, then it shows me only the columns from > verityResults. > > > By the way, should mention that I am running CF 5 on Windows 2003 > Server. Hasn't anybody ever wanted to preserve scores from their > verity searches and then wanted to combine that with database results? > > George > > On 5/3/05, George Abraham <[EMAIL PROTECTED]> wrote: > > It's that last combination that is not working for me. I try to refer > > to the results from the verity resultset joined on the results from > > the database (based on a PrimaryID) and I get an error that the SQL is > > bad. Has anybody ever done this? Here is the SQL code: > > > > SELECT dbResults.*, vr.Score > > FROM dbResults dbr > > INNER JOIN verityResults vr ON vr.key = dbr.PrimaryID > > ORDER BY vr.Score > > > > George > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:205380 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
