January 14, 2008 at 4:35 pm
Hi all,
I am trying to write a SQL query to find the most current address for each customer. I have a customer table, address table, and a state code table. This is part of a search by zip code within a certain mileage range, but that part is working fine and I have stripped it out to simplify my question. Basically, I am getting too many addresses with the query and need to cut it down. I am assuming with a subquery. I have had some luck but usually I cut out too much! Ack!
Here is what I have that works, but returns every address for each customer. I want to only use the latest address (highest AddressID) for each CustomerID
SELECT c.CustomerID , a.AddressID, c.CustomerFirstName, c.CustomerLastName, a.PostalCode AS ZipCode, a.Address, a.address_2, a.City, s.Abbreviation AS State
FROM customers AS c INNER JOIN
Addresses AS a ON a.CustomerID = c.CustomerID INNER JOIN
StateCode AS s ON a.State = s.StateID
WHERE a.status = 'active'
I need to only select ONE AddressID for each CustomerID, but the above query returns all of the active addresses for each CustomerID.
I tried adding this to the WHERE part with no luck. It cuts out too many addresses and some customers completely.
AND a.AddressID = (SELECT MAX(AddressID) FROM Addresses AS a2 WHERE a2.CustomerID = c.CustomerID)
Any help would be greatly appreciated, thanks!!
January 14, 2008 at 5:54 pm
Try rewriting it as a dervied table instead of a CSQ and see if you get more results. If you don't - skip the highlight criteria, turn that oin into an outer join and investigate
SELECT
c.CustomerID , a.AddressID,
c.CustomerFirstName, c.CustomerLastName,
a.PostalCode AS ZipCode, a.Address,
a.address_2, a.City, s.Abbreviation AS State
FROM customers AS c
INNER JOIN
Addresses AS a ON a.CustomerID = c.CustomerID
INNER JOIN
StateCode AS s ON a.State = s.StateID
Inner join
(select CustomerID, max(addressid) as max_add from Addresses group by CustomerID) as MA
on c.customerID=ma.customerID
and a.addressID=mA.Max_add --skip this line is you have trouble and add ma.Max_add to the select.
WHERE a.status = 'active'1
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2008 at 2:01 pm
That helped get me on the right track, got it all running fine now thanks! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply