Multiple rows in one

Question

I know there are similar questions like mine but unfortunately I haven't found the corresponding solution to my problem.

First off here's a simplified overview of my tables:

• Partner table: PartnerID, Name

• Address table: PartnerID, Street, Postcode, City, ValidFrom

• Contact table: PartnerID, TypID, TelNr, Email, ValidFrom

A partner can have one or more addresses as well as contact info. With the contact info a partner could have let's say 2 tel numbers, 1 mobile number and 1 email. In the table it would look like this:

PartnerID   TypID   TelNr                   Email               ValidFrom
1           1       0041 / 044 - 2002020                        01.01.2010
1           1       0041 / 044 - 2003030                        01.01.2011
1           2       0041 / 079 - 7003030                        01.04.2011
1           3                               myemail@hotmail.com 01.06.2011

What I need in the end is, combining all tables for each partner, is like this:

PartnerID   Name    Street              Postcode        City            TelNr                               Email
1           SomeGuy MostActualStreet    MostActualPC    MostActualCity  MostActual Nr (either type 1 or 2)  MostActual Email

Any help?

Answer 1

Here's some T-SQL that gets the answer I think you're looking for if by "Most Actual" you mean "most recent":

WITH LatestAddress (PartnerID,Street,PostCode,City) 
AS (
    SELECT PartnerID,Street,PostCode,City
    FROM [Address] a
    WHERE ValidFrom = (
        SELECT MAX(ValidFrom) 
        FROM [Address] aMax 
        WHERE aMax.PartnerID = a.PartnerID
    )
)

SELECT p.PartnerID,p.Name,la.Street,la.PostCode,la.City
,(SELECT TOP 1 TelNr FROM Contact c WHERE c.PartnerID = p.PartnerID AND TelNr IS NOT NULL ORDER BY ValidFrom DESC) AS MostRecentTelNr
,(SELECT TOP 1 Email FROM Contact c WHERE c.PartnerID = p.PartnerID AND Email IS NOT NULL ORDER BY ValidFrom DESC) AS MostRecentEmail
FROM [Partner] p
LEFT OUTER JOIN LatestAddress la ON p.PartnerID = la.PartnerID

Breaking it down, this example used a common table expression (CTE) to get the latest address information for each Partner

WITH LatestAddress (PartnerID,Street,PostCode,City) 
AS (
    SELECT PartnerID,Street,PostCode,City
    FROM [Address] a
    WHERE ValidFrom = (
        SELECT MAX(ValidFrom) 
        FROM [Address] aMax 
        WHERE aMax.PartnerID = a.PartnerID
    )
)

I left-joined from the Partner table to the CTE, because I didn't want partners who don't have addresses to get left out of the results.

FROM [Partner] p
LEFT OUTER JOIN LatestAddress la ON p.PartnerID = la.PartnerID

In the SELECT statement, I selected columns from the Partner table, the CTE, and I wrote two subselects, one for the latest non-null telephone number for each partner, and one for the latest non-null email address for each partner. I was able to do this as a subselect because I knew I was returning a scalar value by selecting the TOP 1.

SELECT p.PartnerID,p.Name,la.Street,la.PostCode,la.City
,(SELECT TOP 1 TelNr FROM Contact c WHERE c.PartnerID = p.PartnerID AND TelNr IS NOT NULL ORDER BY ValidFrom DESC) AS MostRecentTelNr
,(SELECT TOP 1 Email FROM Contact c WHERE c.PartnerID = p.PartnerID AND Email IS NOT NULL ORDER BY ValidFrom DESC) AS MostRecentEmail

I would strongly recommend that you separate your Contact table into separate telephone number and email tables, each with their own ValidDate if you need to be able to keep old phone numbers and email addresses.

Answer 2

Check out my answer on another post which explains how to get the most actual information in a case like yours : Aggregate SQL Function to grab only the first from each group

PS : The DATE_FIELD would be ValidFrom in your case.