I have the four tables in a database:
CustomerMaster,Addresses,EmailAddresses, & PhoneNumbers
Addresses,EmailAddresses, & PhoneNumbers reference CustomerMaster's primary key.
I would like to create a query to get the most recent customer information for each customer in CustomerMaster. I know how to join the tables together but I don't know how to utilize the TimeStamp fields to retrieve only the most recently updated data from the Addresses,EmailAddresses, and PhoneNumbers tables for each customer in CustomerMaster.
SQL Fiddle: Working Example
CustomerMaster:
| CUS_PK | CUS_FirstName | CUS_LastName | CUS_AccountNum
| 1 | mickey | mouse | 000001
| 2 | donald | duck | 100000
Addresses:
| ADD_CUS_FK | ADD_StreetAddress | ADD_City | ADD_StateProvince | ADD_PostalCode | ADD_TimeStamp
| 1 | Disney World | Orlando | Florida | 99999 | 2000-01-01 12:00:00.000
| 1 | Disney Land | Anaheim | California | 12345 | 2012-12-23 12:00:00.000
| 2 | Disney World | Orlando | Florida | 99999 | 2001-01-01 12:00:00.000
EmailAddresses:
| EMA_CUS_FK | EMA_EmailAddress | EMA_TimeStamp
| 1 | [email protected] | 2005-01-01 12:00:00.000
| 1 | [email protected] | 2006-01-01 12:00:00.000
| 2 | [email protected] | 2001-01-01 12:00:00.000
PhoneNumbers:
| PHO_CUS_FK | PHO_PhoneNumber | PHO_TimeStamp
| 1 | 999-999-9999 | 2001-01-01 12:00:00.000
| 1 | 012-345-6789 | 2013-01-01 12:00:00.000
| 2 | 666-867-5309 | 2001-01-01 12:00:00.000
Query:
SELECT DISTINCT cm.CUS_FirstName, cm.CUS_LastName, cm.CUS_AccountNum,
addr.ADD_StreetAddress, addr.ADD_City,
addr.ADD_StateProvince, addr.ADD_PostalCode,
email.EMA_EmailAddress, phone.PHO_PhoneNumber
FROM CustomerMaster AS cm
JOIN Addresses AS addr
ON cm.CUS_PK = addr.ADD_CUS_FK
JOIN EmailAddresses AS email
ON cm.CUS_PK = email.EMA_CUS_FK
JOIN PhoneNumbers AS phone
ON cm.CUS_PK = phone.PHO_CUS_FK
ORDER BY cm.CUS_AccountNum
Current Result:
Mickey | Mouse | 000001 | Disney World | Orlando | Florida | 99999 | [email protected] | 999-999-9999
Mickey | Mouse | 000001 | Disney World | Orlando | Florida | 99999 | [email protected] | 012-345-6789
Mickey | Mouse | 000001 | Disney World | Orlando | Florida | 99999 | [email protected] | 999-999-9999
Mickey | Mouse | 000001 | Disney World | Orlando | Florida | 99999 | [email protected] | 012-345-6789
Mickey | Mouse | 000001 | Disney Land | Anaheim | California | 12345 | [email protected] | 999-999-9999
Mickey | Mouse | 000001 | Disney Land | Anaheim | California | 12345 | [email protected] | 012-345-6789
Mickey | Mouse | 000001 | Disney Land | Anaheim | California | 12345 | [email protected] | 999-999-9999
Mickey | Mouse | 000001 | Disney Land | Anaheim | California | 12345 | [email protected] | 012-345-6789
Donald | Duck | 100000 | Disney World | Orlando | Florida | 99999 | [email protected] | 666-867-5309
Desired Result:
Mickey | Mouse | 000001 | Disney Land | Anaheim | California | 12345 | [email protected] | 012-345-6789
Donald | Duck | 100000 | Disney World | Orlando | Florida | 99999 | [email protected] | 666-867-5309
How would I need to edit my existing query to get the desired result set?
Please provide an augmented SQL query (preferably a SQL Fiddle)
Apparently, LEFT JOIN and NULL checking was the solution:
Query:
SELECT DISTINCT cm.CUS_FirstName, cm.CUS_LastName, cm.CUS_AccountNum,
addrs1.ADD_StreetAddress, addrs1.ADD_City, addrs1.ADD_StateProvince,
addrs1.ADD_PostalCode, email1.EMA_EmailAddress, phone1.PHO_PhoneNumber
FROM CustomerMaster AS cm
JOIN Addresses AS addrs1
ON cm.CUS_PK = addrs1.ADD_CUS_FK
LEFT JOIN Addresses AS addrs2
ON cm.CUS_PK = addrs2.ADD_CUS_FK
AND addrs1.ADD_TimeStamp < addrs2.ADD_TimeStamp
JOIN EmailAddresses AS email1
ON cm.CUS_PK = email1.EMA_CUS_FK
LEFT JOIN EmailAddresses AS email2
ON cm.CUS_PK = email2.EMA_CUS_FK
AND email1.EMA_TimeStamp < email2.EMA_TimeStamp
JOIN PhoneNumbers AS phone1
ON cm.CUS_PK = phone1.PHO_CUS_FK
LEFT JOIN PhoneNumbers AS phone2
ON cm.CUS_PK = phone2.PHO_CUS_FK
AND phone1.PHO_TimeStamp < phone2.PHO_TimeStamp
WHERE phone2.PHO_TimeStamp IS NULL
AND addrs2.ADD_TimeStamp IS NULL
AND email2.EMA_TimeStamp IS NULL
ORDER BY cm.CUS_AccountNum
SQL Fiddle: here