First off, your entreProfile.profileID = busPlan.profileID is the same for all cases, so you should have it only once in your query to simplify it. That said, here's your solution:
<CFQUERY NAME="links" DATASOURCE="database"> SELECT DISTINCT ON (busPlan.profileID) busPlan.*, entreProfile.* FROM busPlan, entreProfile WHERE entreProfile.profileID = busPlan.profileID <CFIF status IS "new"> AND busPlan.status LIKE "new" <CFELSEIF status IS "under_review"> AND busPlan.status LIKE "under_review" <CFELSEIF status IS "approved"> AND busPlan.status LIKE "approved" <CFELSE> AND busPlan.status LIKE "failed" </CFIF> </CFQUERY> Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Arun Persaud > Sent: Monday, April 08, 2002 1:06 PM > To: [EMAIL PROTECTED] > Subject: RE: [CFTALKTor] Pulling a Distinct Value from one of > two tables > > > Thanks for the suggestion Marc. But let me explain it a > little bit more Rudy. > > Basically I'm trying to create a User Profile Interface. The > user submits their profile and as well as a document. At a > later date they can submit more documents. Therefore, one > profile can have multiple documents associated with it. > That's why I have one table for profile information and > another for document information. The common value between > both tables is the "profileID". > > A profile is created once, but the user can submit a new > document each time thereafter because their profileID is > inserted to the "busPlan" table. Thus the multiple occurances > of the "profileID" in the busPlan table. > > > On the Administration end, these documents are categorized by > the following "new, under review, approved and fail" > > > Basically I have two tables: "entreProfile" and "busPlan". > > I need to pull a value from the "fullName" column from the > "entreProfile" table and the DISTINCT "profileID" value from > the column named "profileID" in the "busPlan" table. > > The need for the DISTINCT "profileID" value from the > "busPlan" table is that the "profileID" value occurs more than once. > > My display page currently shows all occurances of a > "profileID" value. For example, if John Doe submitted 3 > documents, then his name (fullName) value would appear 3 > times (because the "profileID" value occurs 3 times in the > "busPlan" table). > > > > This is my current code which displays all occurances of the > fullName and profileID values: > > <CFQUERY NAME="links" DATASOURCE="database"> > SELECT busPlan.*, entreProfile.* > FROM busPlan, entreProfile > > <CFIF status IS "new"> > WHERE busPlan.status LIKE "new" AND entreProfile.profileID = > busPlan.profileID > > <CFELSEIF status IS "under_review"> > WHERE busPlan.status LIKE "under_review" AND > entreProfile.profileID = busPlan.profileID > > <CFELSEIF status IS "approved"> > WHERE busPlan.status LIKE "approved" AND > entreProfile.profileID = busPlan.profileID > > <CFELSE> > WHERE busPlan.status LIKE "failed" AND entreProfile.profileID > = busPlan.profileID > </CFIF> > </CFQUERY> > > > I know it's a lot, but thanks again for the assistance. I > really appreciate it. > > > > Arun Persaud > [EMAIL PROTECTED] > > > > > > > -----Original Message----- > From: rudy [mailto:[EMAIL PROTECTED]] > Sent: April 8, 2002 12:03 PM > To: [EMAIL PROTECTED] > Subject: Re: [CFTALKTor] Pulling a Distinct Value from one of > two tables > > > > But I'd like to pull the DISTINCT value from the table "busPlan" > > only and not "entreProfile" table. > > > > <CFQUERY NAME="links" DATASOURCE="biobroker"> > > SELECT busPlan.*, entreProfile.* > > FROM busPlan, entreProfile > > WHERE status LIKE "new" > > </CFQUERY> > > arun, could you please indicate which distinct value you > want, and why you > think you couldn't just select from the busPlan table alone? > > no sense involving a second table if you don't need to > > you indicated that you already did this to one table > > > SELECT COUNT(DISTINCT profileID) AS new > > FROM busPlan > > WHERE status LIKE "new" > > and i'm curious why this doesn't work, because this is the > syntax you need, > according to your explanation -- "I'd like to pull the > DISTINCT value from > the table busPlan only" > > > rudy > http://rudy.ca/ > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: "rudy" <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "Arun Persaud" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
<<attachment: winmail.dat>>
