How to create SQL Query to get Primary EmailAddress from Email table?

Posted on 2008-11-07
Last Modified: 2013-12-17
Hi Experts!

How do I join my table or what Query do I need to produce so that everytime my application look at the "Account" table, under column "UserID" it will see the user's Primary Email Address?

-User has many EmailAddress and this is stored under the "Email" table
-User has only 1 login account, Where
:Username (Primary EmailAddress stored inside the "email" table )
:Password is stored in the "Account" table.

Note: use the "isPrimary" bit = 1 to see if the EmailAddress has been set to primary.
create table Account (

	AccountID bigint identity not null, 

	UserID varchar(50) not null, 

	Password varchar(128) not null, 

	AccountNumber int not null unique, 

	rowguid uniqueidentifier default 'newid()' not null unique, 

	ModifiedDate datetime default getdate() not null, 

	constraint PK_Account primary key (AccountID)


create table Email (

	EmailID bigint identity not null, 

	EmailAddress varchar(128) not null, 

	isPrimary bit null, 

	isConfirmed bit null, 

	ConfirmCode varchar(255) null, 

	VerificationCodeDateSent datetime null, 

	ModifiedDate datetime not null, 

	AccountID bigint not null, 

	ProfileID bigint null, constraint PK_Email primary key (EmailID)


Open in new window

Question by:Khou
    LVL 14

    Expert Comment

    try this (replace ? with userID)
    FROM Account
    INNER JOIN Email ON Account.AccountID = Email.AccountID
    WHERE Email.isPrimary = 1 AND Account.UserID = ?

    Open in new window


    Author Comment

    Code runs, but it didn't do what I wanted, maybe my ER are all wrong?

    The client's specification are as follows
    #1 - A "username" and "Password" can be used to login a system.
    #2 - An "Email Address" can become a "username", only when it has been "Confirmed" and set to "PRIMARY"
    #3 - A email can be confirmed/verified when the user clicks on a hyperlink via a email sent by the system.
    #4 - A user can have multiple "Email accounts" under 1 account, but only one account can be used as a username as specified in #2.

    I now have 3 tables (cut down version shown below)

    <UserAccount> Table
    PK AccountID
    -Username??? <--- this is the Primary Email Account from "Email" Table! how to get it here? This always changes according to <Email> isPrimary
    ???????FK EmailID???? <---????????
    <AccountProfile> Table
    PK AccountProfileID
    FK AccountID <---- ???PKFK AccountID???

    <Email> Table
    PK EmailID
    FK ProfileID
    ???PKFK AccountID???

    create table Email (EmailID bigint identity not null, EmailAddress varchar(128) not null, isPrimary bit null, isConfirmed bit null, ProfileID bigint not null, constraint PK_Email primary key (EmailID));
    create table Account (AccountID bigint identity not null, Username varchar(50) not null, Password varchar(128) not null, constraint PK_Account primary key (AccountID));
    create table Profile (ProfileID bigint identity not null, AccountID bigint not null, primary key (ProfileID));
    alter table Email add constraint FKEmail539870 foreign key (ProfileID) references Profile;
    alter table Profile add constraint FKProfile531532 foreign key (AccountID) references Account;

    Open in new window


    Author Comment


    Would this work?  (Want to show both "Primary Email Address" and Password in one TABLE).

    WHERE Account.AccountID = Profile.AccountID AND Profile.ProfileID = Email.ProfileID AND Email.isPrimary = 'true'


    Accepted Solution

    Question PAQ'd, 50 points refunded, and stored in the solution database.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video discusses moving either the default database or any database to a new volume.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    729 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

    21 Experts available now in Live!

    Get 1:1 Help Now