?
Solved

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

Posted on 2013-10-28
8
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 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
Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

 
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
 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

777 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