Link to home
Start Free TrialLog in
Avatar of koila
koilaFlag for Australia

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.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
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of koila

ASKER

Good morning Agx :)

You can do it... I will try this script and I will let you know...
Avatar of koila

ASKER

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!!!
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">
Avatar of koila

ASKER

<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?
Avatar of koila

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>

Open in new window

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
...
Avatar of koila

ASKER

thans.