hi arun > 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.
i'm sorry, i still don't understand, but perhaps i am getting closer > The need for the DISTINCT "profileID" value from the "busPlan" > table is that the "profileID" value occurs more than once. well, that's true of any 1-to-many relationship > 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). is this what you're trying to fix? it sounds more like a CFOUTPUT problem > This is my current code which displays all occurances > of the fullName and profileID values: terry made a nice improvement, which also lends itself to the INNER JOIN syntax, if you wanted to write the query that way still, let's concentrate on what you want to retrieve > SELECT busPlan.*, entreProfile.* > FROM busPlan, entreProfile > WHERE entreProfile.profileID = busPlan.profileID > [ AND conditions... ] this will get multiple profiles, each with multiple documents (of the specified status) now comes the main question -- what do you want to show for the profiles and their documents? if you need any detail from the busPlan table (like name of the document, date, etc.) then that's one thing however, if you only needed the number of documents -- for example, beside each profile you have a link that says "8 documents (click to view details)" -- then you should simplify the query select entireProfile.*, count(*) as NumberOfDocuments otherwise you'll have detail records returning, which you really don't want so, going back to your earlier remark ... > The need for the DISTINCT "profileID" value from the "busPlan" > table is that the "profileID" value occurs more than once. this is where i don't understand you -- each profile is going to have multiple documents, so DISTINCT doesn't really make sense, because in all the documents that belong to each profile, their profileID is going to be the same (all child records of a given parent record have the same foreign key value) COUNT makes more sense, but perhaps you want to show individual document detail, in which case you should use CFOUTPUT GROUP=, which will require an ORDER BY (which you didn't have) also, instead of using "select star" just list the individual fields from each table -- this makes it easier to see what the query is trying to get, and can be substantially more efficient (especially if you don't need some of the returned columns) rudy - 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)
