SQL left join on a table with alternative results - implementing selection order for optimal perform-CodePudding

I would like to join two rather large tables in an optimized way and looking for a solution here.

One table has Customers (with ID as primary key).

This to be left joined with:

Address table has different types of address data - it also has ID as pk, Customer ID as a foreign key but with duplications, type of address for the given row and the address itself.

I would like to implement a selection order - if the most preferred address type (in this case Postal) exists for a customer, then take the this address type else take second preference (in this case Delivery) as the next best and then the third type (Locational) as least preferable. There might be more than 3 options in the real problem, maybe 5-6.

To illustrate my problem:

Customer table:

id	name	data
1	Joe	some data Joe
2	Eva	some data Eva
3	David	some data David
4	Adam	some data Adam

Address Table

id	cust_id	address type	address
1	1	Postal	Postal type address for Joe
2	1	Delivery	Delivery type address for Joe
3	1	Locational	Locational type address for Joe
4	2	Delivery	Delivery type address for Eva
5	2	Locational	Locational type address for Eva
6	3	Locational	Locational type address for David

The desired output would look like this:

id	name	data	address type	address
1	Joe	some data Joe	Postal	Posta type address for Joe
2	Eva	some data Eva	Delivery	Delivery type address for Eva
3	David	some data David	Locational	Locational type address for David
4	Adam	some data Adam	null	null

So for customer 1 (Joe) the Postal type address was selected as it did exists. Then for customer 2 (Eva) type Delivery was selected as the second best option, and finally for customer 3 (David) it was the Locational type address. No address was selected for Adam.

I could figure out some options with a number of steps, eg. joining address table multiple times and then selecting the addresses with CASE WHEN type wrapping query but there must be an easier way to do this.

Here is a fiddle (MS SQL Server) http://sqlfiddle.com/#!18/e0ffce

Thanks in advance

CodePudding user response：

Use a CTE with DENSE_RANK() on your Address table. To pick up Customers that do not have an address, add a COALESCE to the WHERE clause:

WITH CTE AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY cust_id ORDER BY
             CASE WHEN address_type = 'Postal' THEN 1 
                  WHEN address_type = 'Delivery' THEN 2 
                  WHEN address_type = 'Locational' THEN 3 
             ELSE NULL END ASC) AS dr
  FROM Address)
SELECT a.*, b.address_type, b.address
FROM Customer a
LEFT JOIN CTE b 
ON a.id = b.cust_id
WHERE COALESCE(dr, 1) = 1

Result:

id	name	some_data	address_type	address
1	Joe	some data Joe	Postal	Postal type address for Joe
2	Eva	some data Eva	Delivery	Delivery type address for Eva
3	David	some data David	Locational	Locational type address for David
4	Adam	some data Adam	null	null

Demo here.

CodePudding user response：

Using a CTE with DENSE_RANK() on my Address table with an explicit order using CASE WHEN does the trick.

Slight modification of GRIV's answer above:

WITH CTE AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY Cust_ID ORDER BY addr_order ASC) AS rank
  FROM (
    SELECT *, 
       CASE WHEN Address_Type LIKE 'Postal' THEN 1 ELSE
         CASE WHEN Address_Type LIKE 'Delivery' THEN 2 ELSE
           CASE WHEN Address_Type LIKE 'Locational' THEN 3 ELSE null 
           END
         END
       END AS addr_order
    FROM Address
 ) z )
SELECT a.*
  , b.address_type
  , b.address
FROM Customer a
LEFT JOIN CTE b 
  ON a.id = b.cust_id
  AND rank = 1