sorry, error on my reply, should be: SELECT entreProfile.fullName, busPlan.profileID FROM entreProfile INNER JOIN busPlan ON busPlan.profileID = entreProfile.profileID GROUP BY busPlan.profileID, entreProfile.fullName
============================= Mike Lau Web Developer [EMAIL PROTECTED] www.userinmind.com ============================= -----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) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "Mike Lau" <[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)
