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

T-SQL to return latest customer email

I have a two table database:

Customer
Email

I want to return a dataset of all customers and just their latest active email (they can have more than one).

In the Email table there are 5 fields:
EmailID
CustomerID
EmailAddress
DateEntered
EmailStatus

If EmailStatus is 1 (active) I want to order all their associated addresses by DateEntered DESC and grab the top 1. Thus I only return their "latest" email address.

I assume this is needing some kind of subquery.

I'd like the query to simply return (one for each active customer):
CustomerID
CustomerName
EmailAddress

Since they can technically have more than one email address in our system, I am getting back 3 rows for the same customer (if they happen to have 3 active email addresses in our system).

How can I query the email table as part of my overall query to return just 1 row per customer?

Thanks-
Dave
0
tivowatcher
Asked:
tivowatcher
  • 3
  • 2
1 Solution
 
BrandonGalderisiCommented:
Try this:

select c.customerid,c.CustomerName,e.emailaddress
from customer c
join email e
on c.customerid = e.customerid
and emailid = (select top 1 emailid from email where customerid = c.customerid and emailstatus=1 order by dateentered desc)
0
 
tivowatcherAuthor Commented:
Just to be clear, what happens if the customer has no associated email address?

I am following you on the JOIN and the subquery and I bet that works, but should this be a LEFT JOIN to ensure I get the customer info even if they have no email?

Thanks!
Dave
0
 
BrandonGalderisiCommented:
Correct.  If the customer can possibly have no email, it should be a left join.  I missed that in your original post.
0
 
tivowatcherAuthor Commented:
Got it (I think).

Will this work? It seems to in my testing:

SELECT     c.CustomerID, e.EmailAddress
FROM         Customer c LEFT OUTER JOIN
                      Email e ON c.CustomerID = e.CustomerID AND e.EmailID =
                          (SELECT     TOP 1 emailid
                            FROM          email
                            WHERE      customerid = c.customerid
                            ORDER BY dateentered DESC)
0
 
BrandonGalderisiCommented:
Yes.
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 Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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