?
Solved

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

Posted on 2008-11-07
5
Medium Priority
?
907 Views
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

0
Comment
Question by:Khou
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Binuth
ID: 22911396
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
 

Author Comment

by:Khou
ID: 22913747
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
 

Author Comment

by:Khou
ID: 22916447
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
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 23293958
Question PAQ'd, 50 points refunded, and stored in the solution database.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 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