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

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

0
Khou
Asked:
Khou
  • 2
1 Solution
 
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
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.

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