SQLTeam.com | Weblogs | Forums

How to use a table based on condition only when a field value is not available?

Transact-SQL
#1

I have two tables (Contact_Table and Order_Table) and I have current using a query like this

SELECT a.CustID, a.EmailAddress, a.Contact, a.Country, b.OrderID
FROM a.Contact_Table a
INNER JOIN b.Order_Table b
ON b.CustID = a.CustID

Contact_Table

CustID EmailAddress Contact Country
9909 abc1@email1.com Maria Anders Germany
7636 abc2@email2.com Francisco Chang Mexico
1927 Roland Mendel Austria

Order_Table

CustID OrderID
9909 123
7636 1234

But the issue is, I do not have email address value for all contacts in Contact_Table

I have a 3rd table - Contact_Table_HistoricalData

Contact_Table_HistoricalData

CustID EmailAddress Contact
9909 abc1@email1.com Maria Anders
7636 abc2@email2.com Francisco Chang
1927 abc3@email3.com Roland Mendel

How can I use this Contact_Table_HistoricalData table in my query for records where email address is NOT available then get email address value from Contact_Table_HistoricalData table?

#2

SELECT a.CustID, ISNULL(b.EmailAddress,a.EmailAddress) AS Emailaddress, a.Contact, a.Country, b.OrderID
FROM a.Contact_Table a
INNER JOIN b.Order_Table b
ON b.CustID = a.CustID
LEFT OUTER JOIN Contact_Table_HistoricalData c
ON c.CustID = a.CustID

But this only works as each CustID has 1 record in Contact_Table_HistoricalData, CustID must be unique.

#3

If your historical table can have more than one row per customer then using an outer join is going to return 'duplicate' rows. If that is the case, you can convert that outer join to an outer apply with a select top 1 - and hopefully you have some date/time column on that historical table to determine the most recent value:

SELECT a.CustID
     , EmailAddress = coalesce(nullif(b.EmailAddress, ''), hd.EmailAddress, 'Address Not Found')
     , a.Contact
     , a.Country
     , b.OrderID
  FROM Contact_Table                        As a
 INNER JOIN Order_Table                     As b ON b.CustID = a.CustID
 OUTER APPLY (SELECT TOP 1
                     h.EmailAddress
                FROM Contact_Table_HistoricalData h
               WHERE h.CustID = c.CUSTID
                 AND h.EmailAddress > ''
               ORDER BY
                     h.SomeDateColumn desc) As hd

Use COALESCE instead of ISNULL - we can then evaluate email address from each table and get the first non-null value. Adding in the NULLIF will return a null value if the EmailAddress is blank (empty string) and not an actual null value.

We can filter out values from the historical data by checking for EmailAddress > ''. So if that customer has 10 rows in history and the first 3 don't have an email address - this would pull the 4th row and use that value if the current value is also empty.

#4 

SELECT a.CustID, 
    ISNULL(NULLIF(a.EmailAddress, ''), c.EmailAddress) AS EmailAddress, 
    a.Contact, a.Country, b.OrderID
FROM dbo.Contact_Table a
INNER JOIN dbo.Order_Table b
ON b.CustID = a.CustID
OUTER APPLY (
    SELECT TOP (1) c.*
    FROM dbo.Contact_Table_HistoricalData c
    WHERE (a.EmailAddress IS NULL OR a.EmailAddress = '') AND /* don't need to waste time on this lookup if the a table already has an emailaddress */
        c.CustID = a.CustID AND
        c.EmailAddress <> '' 
    ORDER BY c.DateTimeColumn /*or c.$IDENTITY*/ DESC
) AS c
#5

Hi @RogierPronk

In the sample query that you shared, what is email address is NOT found in Contact_Table_HistoricalData?

Currently, I have EmailAddress field as primary key NOT NULLABLE but getting error with the sample query - Cannot insert a NULL value into a non-nullable column.

SELECT a.CustID, ISNULL(b.EmailAddress,a.EmailAddress) AS Emailaddress, a.Contact, a.Country, b.OrderID
FROM a.Contact_Table a
INNER JOIN b.Order_Table b
ON b.CustID = a.CustID
LEFT OUTER JOIN Contact_Table_HistoricalData c
ON c.CustID = a.CustID
#6

Hi @jeffw8713 in your approach, if I do not want to assign a fall back value like 'Address Not Found' and if email address not found in Contact_Table_HistoricalData then do not even bring the contact row in the output as email address is a requirement and not nullable in my case.

Also - curious, what exactly this is doing?
h.EmailAddress > ' '

#7

Based on your feedback I think I made a mistake and it should have been

ISNULL(a.EmailAddress,c.EmailAddress)

Basically it means that when the e-mailaddress is NULL in table a, he will use the EmailAddress from table c. Contact_Table_HistoricalData. It only works when there is 1 record in table a for each Custid and 1 or 0 record of history in c as otherwise data get doubled.

If you want to excluded the empty emailaddresses you can use the were clausule

WHERE ISNULL(a.EmailAddress,c.EmailAddress) IS NOT NULL.

#8

I updated my original post - noticed I had entered Contact_Table twice when it should have been Order_Table.

You can just add a where clause to exclude any rows where you don't have a valid email address from either current or history.

 WHERE a.EmailAddress > ''
    OR hd.EmailAddress > ''

As for what is the check for '' doing - it is really simple. If the value in the column is NULL - it excludes those values because NULL is unknown. It also excludes any columns where the value is an empty string.