Return values from different tables based on a priority

Question

Assuming I have three tables : TableA (key, value) TableB (key, value) TableC (key, value)

and I want to return a value for all keys. If the key exists in TableC return that value else if the key exists in B return that value else return the value from table A

The best I have come up with so far is

SELECT key,Value
FROM TableA
WHERE key NOT IN (SELECT key FROM TableB)
    AND key NOT IN (SELECT key FROM TableC)
UNION
SELECT key,Value
FROM TableB
WHERE key NOT IN (SELECT key FROM TableC)
UNION
SELECT key,Value
FROM TableC

But this seems pretty brute force. Anyone know a better way?

Edit: Here is a more concrete example. Consider TableA as a standard work schedule where the key is a date and the value is the assigned shift. Table B is a statutory holiday calendar that overrides the standard work week. Table C is an exception schedule that is used to override the other two schedules when someone is asked to come in and work either an extra shift or a different shift.

Answer 1

If you have a number of tables that you want data from, then you are going to have to select from them, there is no other way around it.

From your SQL, it seems that you could get restuls from tableC that contains keys in tableA and tableB, as you are UNION-ing the restuls of a simple select on tableC (of which there is no where clause). Where you after an exclusive set of keys that do NOT exist in any of the other tables? If so, then you will need to do what you did for the where clause for tableA in the selects for tableB and tableC.

I hope that makes sense...

Answer 2

Your query looks fine.

Alternatively, you can use the query below and filter on the client side. It will be less stressful for the database server.

SELECT key, value, 2 AS priority
FROM TableA
UNION
SELECT key, value, 1 AS priority
FROM TableB
UNION
SELECT key, value, 0 AS priority
FROM TableC
ORDER BY key, priority

Answer 3

Here's how I would do it in SQL Server. This solution should generate less logical IO than the original. If the tables were sufficiently huge, I would swap over to #temp tables to enable parallelism.

DECLARE @MyTable TABLE
(
  Key int PRIMARY KEY,
  Value int
)

    --Grab from TableC
INSERT INTO @MyTable(Key, Value)
SELECT Key, Value
FROM TableC

    --Grab from TableB
INSERT INTO @MyTable(Key, Value)
SELECT Key, Value
FROM TableB
WHERE Key not in (SELECT Key FROM @MyTable)

    --Grab from TableA  
INSERT INTO @MyTable(Key, Value)
SELECT Key, Value
FROM TableA
WHERE Key not in (SELECT Key FROM @MyTable)
    --Pop the result
SELECT Key, Value
FROM @MyTable

This technique mirrors how I would handle 3 lists in C#... by creating a dictionary.

Answer 4

Here is an alternate SQL statement:-

SELECT
    ALL_KEYS.KEY,
    NVL( TABLEC.VALUE, NVL( TABLEB.VALUE, TABLEA.VALUE)) AS VALUE
FROM
    (SELECT KEY AS KEY FROM TABLEA
     UNION
     SELECT KEY FROM TABLEB
     UNION
     SELECT KEY FROM TABLEC) ALL_KEYS,
     TABLEA,
     TABLEB,
     TABLEC
WHERE
    ALL_KEYS.KEY = TABLEA.KEY(+) AND
    ALL_KEYS.KEY = TABLEB.KEY(+) AND
    ALL_KEYS.KEY = TABLEC.KEY(+);

NB. The NVL() is an Oracle function. If the first parameter is NULL, the second parameter is returned otherwise the first parameter is returned. You didn't say which database you were using but no doubt there are equivalents in everything.

Answer 5

OK, using your concrete example as a basis, I came up with a solution different from the others posted (although I think I like your solution better). This was tested on MS SQL Server 2005 - changes may be needed for your SQL dialect.

First, some DDL to set the stage:

CREATE TABLE [dbo].[StandardSchedule](
    [scheduledate] [datetime] NOT NULL,
    [shift] [varchar](25) NOT NULL,
 CONSTRAINT [PK_StandardSchedule] PRIMARY KEY CLUSTERED 
( [scheduledate] ASC ));

CREATE TABLE [dbo].[HolidaySchedule](
    [holidaydate] [datetime] NOT NULL,
    [shift] [varchar](25) NOT NULL,
 CONSTRAINT [PK_HolidaySchedule] PRIMARY KEY CLUSTERED 
( [holidaydate] ASC ));

CREATE TABLE [dbo].[ExceptionSchedule](
    [exceptiondate] [datetime] NOT NULL,
    [shift] [varchar](25) NOT NULL,
 CONSTRAINT [PK_ExceptionDate] PRIMARY KEY CLUSTERED 
( [exceptiondate] ASC ));

INSERT INTO ExceptionSchedule VALUES ('2008.01.06', 'ExceptionShift1');
INSERT INTO ExceptionSchedule VALUES ('2008.01.08', 'ExceptionShift2');
INSERT INTO ExceptionSchedule VALUES ('2008.01.10', 'ExceptionShift3');
INSERT INTO HolidaySchedule VALUES ('2008.01.01', 'HolidayShift1');
INSERT INTO HolidaySchedule VALUES ('2008.01.06', 'HolidayShift2');
INSERT INTO HolidaySchedule VALUES ('2008.01.09', 'HolidayShift3');
INSERT INTO StandardSchedule VALUES ('2008.01.01', 'RegularShift1');
INSERT INTO StandardSchedule VALUES ('2008.01.02', 'RegularShift2');
INSERT INTO StandardSchedule VALUES ('2008.01.03', 'RegularShift3');
INSERT INTO StandardSchedule VALUES ('2008.01.04', 'RegularShift4');
INSERT INTO StandardSchedule VALUES ('2008.01.05', 'RegularShift5');
INSERT INTO StandardSchedule VALUES ('2008.01.07', 'RegularShift6');
INSERT INTO StandardSchedule VALUES ('2008.01.09', 'RegularShift7');
INSERT INTO StandardSchedule VALUES ('2008.01.10', 'RegularShift8');

Using these tables/rows as a basis, this SELECT statement retrieves the desired data:

SELECT DISTINCT
    COALESCE(e2.exceptiondate, e.exceptiondate, holidaydate, scheduledate) AS ShiftDate,
    COALESCE(e2.shift, e.shift, h.shift, s.shift) AS Shift
FROM standardschedule s
FULL OUTER JOIN holidayschedule h ON s.scheduledate = h.holidaydate
FULL OUTER JOIN exceptionschedule e ON h.holidaydate = e.exceptiondate
FULL OUTER JOIN exceptionschedule e2 ON s.scheduledate = e2.exceptiondate
ORDER BY shiftdate

Answer 6

SELECT isnull( c.key, isnull( b.key, a.key) ) , 
       isnull( c.value, isnull( b.value, a.value ) ) 
FROM   TableA a 
LEFT JOIN TableB b 
ON        a.key = b.key
LEFT JOIN TableC c 
ON        b.key = c.key

Answer 7

Create a master table of all keys, then left join this master table to the three tables and investigate the COALESCE command.