asked on

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

Binuth

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

Khou

ASKER

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

Khou

ASKER

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

ASKER CERTIFIED SOLUTION

ee_auto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial