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
LVL 4
koilaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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#

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
koilaAuthor Commented:
I will test it    Thanks
koilaAuthor Commented:
FYI.  Is not the same id.  
WHERE enrollee_id_number  = #client.user_id#
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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
koilaAuthor Commented:
Would you please help me on this questions.
koilaAuthor Commented:
Could you please review this question below.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.