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.username#" password="#application.password#">
    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
LVL 4
koilaAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
If you must match on user id, you need a JOIN between user and enrollees.  Assuming the "user_id" column is an INTEGER, something like this where #someUserIDHere# represents the user id, ie 16885

(Always use cfqueryparam to protect the db from sql injection)

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="#someUserIDHere#" cfsqltype="cf_sql_integer">
0
 
koilaAuthor Commented:
Good morning Agx :)

You can do it... I will try this script and I will let you know...
0
 
koilaAuthor Commented:
Right now into my code I have this script.

<CFQUERY NAME="DisplayEmail" datasource="aar" username="#application.username#" password="#application.password#">
    SELECT *
    FROM email
    WHERE email.user_id = #client.user_id#
    </CFQUERY>

Open in new window


<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!!!
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
_agx_Commented:
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.username#" password="#application.password#">
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">
0
 
koilaAuthor Commented:
<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>

<input type="text" name="email" value="#DisplayEmail.official_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?
0
 
koilaAuthor Commented:
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>

Open in new window

0
 
_agx_Commented:
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
...
0
 
koilaAuthor Commented:
thans.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.