Help for Query

[AKRUP]

Hi, All, Need a Help!
I am using three tables, called ?WEB_SITE ? , ?USER_INFO? and ?HEALTHFORM?.
The user who login from USER_INFO, wants to see all the data in HEALTHFORM
filled by him. One user can filled many Health Forms. The user who fills the
health form will get one new Web_ ID from WEB_SITE every time. Trying to call a
QUERY or STORED PROCEDURE to retrieve all the data by user name and password. I
am trying to make a web site like net doctor and using cold fusion. Any help
would be appreciated.

[Tomdogggg]

It sounds like,(by the way you are explaining what you are trying to do), that
you do not need the WEB_SITE table at all, you can just use 2 tables instead if
you do it a different way. Just make sure there's a field in the HEALTHFORM
table so that it will store the USER ID from the USER_INFO table. When the
user submits the health form, have it pull his current loggedin ID and insert
it into the healthform. If you make a query as follows, assuming the field you
put into the healthform to see who put it in is USER_ID and that you are
holding the logged in persons ID in SESSION.userID:

<cfquery name="getUserHealthForms" datasource="your datasource name here">
SELECT *
FROM HEALTHFORM
WHERE USER_ID=#SESSION.userID#
</cfquery>


If you are looking to do the entire thing at once then it would look similar
to the following:

<cfquery name="getUserHealthForms" datasource="your datasource name here">
SELECT ui.*, hf.*
FROM USER_INFO ui, HEALTHFORM hf
WHERE 0=0
<cfif NOT IsDefined("SESSION.userPassword")>
AND ui.userPassword="#FORM.password#"
<cfelse>
AND ui.userPassword="#SESSION.userPassword#"
</cfif>
<cfif NOT IsDefined(SESSION.userName)>
AND ui.userName="#FORM.username#"
<cfelse>
AND ui.userName="#SESSION.userName#"
</cfif>
AND hf.USER_ID=ui.userName
</cfquery>

<!---delete this entire line of text if you would like to see what the query
has retrieved for debugging
<cfdump var="getUserHealthForms">
--->