Solved

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

Posted on 2013-10-28
8
230 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

806 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