[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

T-SQL to return latest customer email

Posted on 2008-11-04
5
Medium Priority
?
207 Views
Last Modified: 2012-05-05
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
Comment
Question by:tivowatcher
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22877316
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
 

Author Comment

by:tivowatcher
ID: 22877948
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
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22878022
Correct.  If the customer can possibly have no email, it should be a left join.  I missed that in your original post.
0
 

Author Comment

by:tivowatcher
ID: 22878030
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22878066
Yes.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
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. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 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