koila
asked on
how do you add a select statement to a where statement in SQL.
I have three table in my Database. Email, enrollees and users.
Please view attached picture to get a better understanding. I need to retrieve the 'official_email' data field from the 'enrollees' table. I have the following code but it missing the select statement to where = select ....
I have this case. Into my code the Enrolle id_number is 972432 is not equal to the client.user_id which is 16885. How I can retrieve the 'official email' data from that field into the 'enrollees' table??
<CFQUERY NAME="DisplayEmail" datasource="aar" username="#application.use rname#" password="#application.pas sword#">
SELECT official_email
FROM enr
WHERE enrollee_id_number = #client.user_id#
</CFQUERY>
Please advise, how I can add another select to the where = select .... .
EmailTableResults.jpg
EnrolleesTableResults.jpg
UsersTableResults.jpg
Please view attached picture to get a better understanding. I need to retrieve the 'official_email' data field from the 'enrollees' table. I have the following code but it missing the select statement to where = select ....
I have this case. Into my code the Enrolle id_number is 972432 is not equal to the client.user_id which is 16885. How I can retrieve the 'official email' data from that field into the 'enrollees' table??
<CFQUERY NAME="DisplayEmail" datasource="aar" username="#application.use
SELECT official_email
FROM enr
WHERE enrollee_id_number = #client.user_id#
</CFQUERY>
Please advise, how I can add another select to the where = select .... .
EmailTableResults.jpg
EnrolleesTableResults.jpg
UsersTableResults.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Right now into my code I have this script.
<td height="21">E-Mail:</td>
<td height="21"><cfoutput>
<input type="text" name="email" value="#DisplayEmail.email #" size="50">
Based on these tables attached, please do you know how to change that cfquery?
Thank you very much for your help!!!
<CFQUERY NAME="DisplayEmail" datasource="aar" username="#application.username#" password="#application.password#">
SELECT *
FROM email
WHERE email.user_id = #client.user_id#
</CFQUERY>
<td height="21">E-Mail:</td>
<td height="21"><cfoutput>
<input type="text" name="email" value="#DisplayEmail.email
Based on these tables attached, please do you know how to change that cfquery?
Thank you very much for your help!!!
If you want to use official_email instead of "email", use the query I posted. But substitute #client.user_id# for #someUserIDHere#.
<CFQUERY NAME="DisplayEmail" datasource="aar" username="#application.use rname#" password="#application.pas sword#">
SELECT e.official_email
FROM Users u INNER JOIN Enroless e ON e.enrollee_id_number = u.id_number
WHERE u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer" >
</cfquery>
Also change the query column name in the output:
<input type="text" name="email" value="#DisplayEmail.official_email#" size="50">
<CFQUERY NAME="DisplayEmail" datasource="aar" username="#application.use
SELECT e.official_email
FROM Users u INNER JOIN Enroless e ON e.enrollee_id_number = u.id_number
WHERE u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
</cfquery>
Also change the query column name in the output:
<input type="text" name="email" value="#DisplayEmail.official_email#" size="50">
ASKER
<CFQUERY NAME="DisplayEmail" datasource="aar" username="#application.use rname#" password="#application.pas sword#">
SELECT e.official_email
FROM Users u INNER JOIN Enrolless e ON e.enrollee_id_number = u.id_number
WHERE u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer" >
</cfquery>
<input type="text" name="email" value="#DisplayEmail.offic ial_email# " size="50">
Here is what I've received.
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'Enroless'. <br>The error occurred on line 69.
I have this on line 69:
WHERE u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer" >
Please let me know if you want me to upload the full code file?
SELECT e.official_email
FROM Users u INNER JOIN Enrolless e ON e.enrollee_id_number = u.id_number
WHERE u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
</cfquery>
<input type="text" name="email" value="#DisplayEmail.offic
Here is what I've received.
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'Enroless'. <br>The error occurred on line 69.
I have this on line 69:
WHERE u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
Please let me know if you want me to upload the full code file?
ASKER
Here is all the coldfusion querries I have it in my page.
<CFQUERY NAME="DisplayUser" datasource="aar" username="#application.username#" password="#application.password#">
SELECT *
FROM users
WHERE users.user_id = #client.user_id#
</CFQUERY>
<!---<CFQUERY NAME="DisplayVolunteer" datasource="aar" username="#application.username#" password="#application.password#">
SELECT *
FROM volunteers
WHERE users.user_id = #client.user_id#
</CFQUERY>--->
<CFQUERY NAME="DisplayCVITool" datasource="aar" username="#application.username#" password="#application.password#">
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.ID
,cvitool.AF_YR
,cvitool.CR_YR
,cvitool.Profile_updated
,cvitool.CMTEE_PREF
,cvitool.ISSUES
,cvitool.PRACTICE_AREA_SPEC
,cvitool.YEARS_EXP
,cvitool.PRACTICE_AREA_PAST
,cvitool.OTHER_ACT_VOL
,cvitool.OTHER_NONACT_VOL
,cvitool.SPEC_INT
,cvitool.SPEC_SKILLS
FROM users INNER JOIN cvitool ON users.id_number = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
</CFQUERY>
<CFQUERY NAME="DisplayCVIToolvolunteer" datasource="aar" username="#application.username#" password="#application.password#">
SELECT volunteers.user_id
,volunteers.id_number
,volunteers.firstname
,volunteers.lastname
,volunteers.NewProfileUpdated
,volunteers.contactme
,volunteers.CMTEE_PREF_NEW
,volunteers.ISSUES
,volunteers.PRACTICE_AREA_SPEC
,volunteers.YEARS_EXP
,volunteers.otherActVolWork
,volunteers.OtherNonActVolWork
,volunteers.SPEC_INT
,volunteers.SPEC_SKILLS_NEW
,volunteers.CIAContactedYou
,volunteers.CIAPermissionProfile
FROM volunteers
WHERE volunteers.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
</CFQUERY>
<!---<CFQUERY NAME="DisplayEmail" datasource="aar" username="#application.username#" password="#application.password#">
SELECT *
FROM email
WHERE email.user_id = #client.user_id#
</CFQUERY>--->
<CFQUERY NAME="DisplayEmail" datasource="aar" username="#application.username#" password="#application.password#">
SELECT e.official_email
FROM Users u INNER JOIN Enrolless e ON e.enrollee_id_number = u.id_number
WHERE u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
</cfquery>
<cfparam name="Task" default="">
<cfif Task eq "UpdateInfo">
<CFQUERY NAME="UpdateInfo" datasource="aar" username="#application.username#" password="#application.password#">
<CFIF #FORM.user_id# NEQ "" OR #FORM.LoginName# NEQ "" OR #FORM.firstname# NEQ "" OR #FORM.lastname# NEQ "" OR #FORM.password# NEQ "" OR #FORM.hint# NEQ "">
<CFIF #FORM.LoginName# NEQ "">
UPDATE users
SET
login = '#LoginName#'
WHERE users.user_id=#user_id#
</CFIF>
<CFIF #FORM.firstname# NEQ "">
UPDATE users
SET
firstname = '#firstname#'
WHERE users.user_id=#user_id#
</CFIF>
<CFIF #FORM.lastname# NEQ "">
UPDATE users
SET
lastname = '#FORM.lastname#'
WHERE users.user_id=#FORM.user_id#
</CFIF>
<CFIF #FORM.password# NEQ "">
UPDATE users
SET
password = '#FORM.password#'
WHERE users.user_id=#FORM.user_id#
</CFIF>
<CFIF #FORM.hint# NEQ "">
UPDATE users
SET
hint = '#FORM.hint#'
WHERE users.user_id=#FORM.user_id#
</CFIF>
<CFIF #FORM.id_number# NEQ "">
UPDATE users
SET
id_number = #FORM.id_number#
WHERE users.user_id=#FORM.user_id#
</CFIF>
<!---<CFIF #FORM.CMTEE_PREF# NEQ "">
UPDATE cvitool
SET
CMTEE_PREF = #FORM.CMTEE_PREF#
WHERE users.user_id=#FORM.user_id#
</CFIF>--->
</CFIF>
</CFQUERY>
<cflocation url="../security/index_e.cfm" addtoken="no">
</CFIF>
My bad. Looks like there's a typo in the table name. Just correct it by plugging in the real name of your enrollees table here:
...
FROM Users u INNER JOIN Enrolless e ON e.enrollee_id_number = u.id_number
...
...
FROM Users u INNER JOIN Enrolless e ON e.enrollee_id_number = u.id_number
...
ASKER
thans.
ASKER
You can do it... I will try this script and I will let you know...