complex query issue

I'm having some difficulty with a query I'm trying to set up. What I'm trying to do is set up a query that will populate a table that shows a list of all tests offered on the site that are active, and then show the completion status of the user who's logged in.

The tests and user scores are all stored in a database, with the appropriate tables as so (names changed for simplicity):program: list of tests by program number and titleevaluation: records of each user's responses to a pre-test surveyexam: records of each user's responses, score, and date a certificate of completion was issued upon passing

Of course, querying each is a simple matter. Setting up a join is a bit troublesome, not only because of ColdFusion's eccentricities. First, there is not a foreign key relationship between any of these tables (I didn't design this db, I only inherited it!). Second, not all of the entries in the table "program" will exist in either "evaluation" or "exam" for the user logged in for that session. The difficulty I've had in trying to join or do a Query by Query is that I can get a list of all that a user has an entry for in either or both the evaluation or exam. However, getting a result that has the master list AND this list is escaping me. At this point, I have a master list and a status for about half of the results I need, but about half are not being pulled in by the query.

This has gotten me the closest to the result I'm trying to end up with. If I do the <cfoutput> without the group, I get duplicates for every instance of each program number (including those for users not logged in), though only the result for the session user actually shows up in its appropriate line. When grouped, I think it is only showing the first or last entry it finds for all, rather than for the session user. I tried to do a query of that query to filter out the results for anyone but the session user, but the resulting records do not include those for which the session user has no entry. I can go over the other ways I've attacked this problem and the results I've gotten if it would help.

look for the duplicates just make you query output like this
<cfoutput query="user_completed" group="program_id" maxrow="1">

Thanks for your response. That limited the output to the first record it encountered. So instead of about 30 entries from our master list, only one shows up. I tried it also without the group="program_id" attribute, and got the same result.

look my friend, i don't know how is your login table related to your database but i think you can play with session.userid
ether by putting if statement before you output the query or by having the session.userid with query like
and where userid=session.userid
hope it help you.

Hi everybody!
Could you teach me how to get back the “primary key”: (“id”), from an “insert query”?
*
[code=coldfusion]<cffunction name="insertData" access="public">
<cfargument name="formData" type="struct" …

I have a 2d matrix with dimension (3, n) called A, I want to calculate the normalization and cross product of two arrays (b,z) (see the code please) for each column (for the first column, then the second one and so on).
the function that I created to find the ...

I'm working in Excel to extract content of a table from a web page and populate Excel sheet cells with it. I'm trying to extract data from a table that gets loaded to a web page after show results method gets called. I have an error that says "Run-time error ...