• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Select a field from a table in coldfusion.

I have a online form and I'm displaying the email field from the email table.  This field is not valid anymore.  So here is the current script.  Please see attached images.

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

   <tr>
              <td height="21">E-Mail:</td>
              <td height="21"><cfoutput>
              #DisplayEmail.email#
                  <input type="hidden" name="email" value="#DisplayEmail.email#">
                </cfoutput>      

Now I want do a change and I would like to display the 'official_email' from the ENR table.
I've tried and it didn't work.

FYI, the enrollee_id_number is primary key into the ENR table which is identical to the foreign key id_number in users table.

How can I do it.. .

  SELECT official_email
    FROM enr, users
    where enr.enrollee_id_number = select .....
ENR.jpg
users.jpg
emailtable.jpg
0
koila
Asked:
koila
  • 6
1 Solution
 
gdemariaCommented:
You could replace the existing query with this..

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


If you need to join them


SELECT u.*
          , e.official_email
    FROM users u
       left join enr   e  on e.enrollee_id = u.user_id
    WHERE u.user_id  = #client.user_id#
0
 
koilaAuthor Commented:
I will test it    Thanks
0
 
koilaAuthor Commented:
FYI.  Is not the same id.  
WHERE enrollee_id_number  = #client.user_id#
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
koilaAuthor Commented:
Good morning gdemaria,

Please see attached scripts results into my comments.  So this is an example for this username .

I need to retrieve this field 'official_email' from my database enrollees... Maybe you have to do select into select.. not sure.  .

Into your code below the Enrolle id_number is 972432 will be not equal to the client.use_id which is 16885

<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. thanks!
EmailTableResults.jpg
EnrolleesTableResults.jpg
UsersTableResults.jpg
0
 
koilaAuthor Commented:
Would you please help me on this questions.
0
 
koilaAuthor Commented:
Could you please review this question below.
0
 
koilaAuthor Commented:
Please note that you have respond to my question in general but to not my specific question querry.  I will post this in a separate question, if you are intested please change the script according to the requirements.

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

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now