Solved

how do you add a select statement to a where statement in SQL.

Posted on 2013-10-28
8
227 Views
Last Modified: 2014-01-21
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
0
Comment
Question by:koila
  • 5
  • 3
8 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 39606023
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
 
LVL 4

Author Comment

by:koila
ID: 39606058
Good morning Agx :)

You can do it... I will try this script and I will let you know...
0
 
LVL 4

Author Comment

by:koila
ID: 39606067
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
 
LVL 52

Expert Comment

by:_agx_
ID: 39606098
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 4

Author Comment

by:koila
ID: 39606168
<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
 
LVL 4

Author Comment

by:koila
ID: 39606190
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
 
LVL 52

Expert Comment

by:_agx_
ID: 39606275
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
 
LVL 4

Author Closing Comment

by:koila
ID: 39797896
thans.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now