Just my rookie thinking but couldn't you use the document name, along with
the profileid in the busplan table to create a "distinct" key? OR better
still.... assign a "BusPlan" sequence number to each instance of a
profileid?
i.e.
UserBob, Seq1
UserBob, Seq2
UserJohn, Seq1
UserJohn, Seq2
UserJohn, Seq3
UserAdam, Seq1
You can also of course assign a status to each as well.
That way you can see which docs an user has associated with them and then
pull the specific doc using profileID and userSeqNum.
Hope I've understood you dilemma and that my 2 cents helps in some way.
Karl
> From: "Arun Persaud" <[EMAIL PROTECTED]>
> Reply-To: [EMAIL PROTECTED]
> Date: Mon, 8 Apr 2002 15:10:49 -0400
> To: <[EMAIL PROTECTED]>
> Subject: RE: [CFTALKTor] Pulling a Distinct Value from one of two tables
>
> Thanks for the help guys.
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of [EMAIL PROTECTED]
> Sent: April 8, 2002 1:28 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [CFTALKTor] Pulling a Distinct Value from one of two tables
>
>
> 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)
> -
> 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: Karl Zarudny <[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)
