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

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

KhouAsked:
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.

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

Open in new window

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

The client's specification are as follows
------------------------------------------------------------------------------------------------------------------
Specification:
#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
-Password
???????FK EmailID???? <---????????
<AccountProfile> Table
PK AccountProfileID
-Status
FK AccountID <---- ???PKFK AccountID???

<Email> Table
PK EmailID
-EmailAddress
-isPrimary
-isConfirmed
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

Entity-Relationship-Diagram1.jpg
0
KhouAuthor Commented:
UPDATED DIAGRAM:

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

SELECT
  Person.Profile.AccountID,
  Person.Account.AccountID,
  Person.Account.Password,
  Person.Profile.ProfileID,
  Person.Email.ProfileID,
  Person.Email.EmailAddress,
  Person.Email.isPrimary
FROM
  Person.Account,
  Person.Email,
  Person.Profile
WHERE Account.AccountID = Profile.AccountID AND Profile.ProfileID = Email.ProfileID AND Email.isPrimary = 'true'
 

ER101108.jpg
0
ee_autoCommented:
Question PAQ'd, 50 points refunded, and stored in the solution database.
0

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
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
.NET Programming

From novice to tech pro — start learning today.