Ocean Ocean - 1 month ago 6
SQL Question

How to get data preferentially from the joined tables in SQL Server

I am working in SQL Server. I have three tables,

TableA
,
TableB
and
TableMain
, with
Date
,
StartTime
and
FinishTime
columns.

I have to write a SQL statement which has to pull these three column values from
TableMain
. If the same date is present in the
TableA
, then the
StartTime
value should come from it, otherwise just use the value from
TableMain
. Similarly, if a
FinishTime
is present in the
TableB
, then in the final output that value should replace the one in
TableMain
.

I would like to do it by joining these three tables. The following shows the data in these three tables and the final expected query output.

TableA:

Date StartTime
10/14/2016 11:00 AM
10/16/2016 10:00 AM
10/18/2016 11:30 AM


TableB:

Date FinishTime
10/15/2016 3:00 PM
10/16/2016 4:00 PM
10/17/2016 6:30 PM
10/18/2016 5:00 PM


TableMain:

Date StartTime FinishTime
10/14/2016 8:00 AM 10:00 PM
10/15/2016 8:00 AM 10:00 PM
10/16/2016 8:00 AM 10:00 PM
10/17/2016 8:00 AM 10:00 PM
10/18/2016 8:00 AM 10:00 PM


Desired output:

10/14/2016 11:00 AM 10:00 PM
10/15/2016 8:00 AM 3:00 PM
10/16/2016 10:00 AM 4:00 PM
10/17/2016 8:00 AM 6:30 PM
10/18/2016 11:30 AM 5:00 PM

Answer

Truly untested but something like this:

SELECT
   t.DateVal,
   t.Start,
   t.Finish
FROM
(
SELECT
   tm.DateVal AS DateVal,
   CASE WHEN a.Start IS NOT NULL THEN a.Start ELSE tm.Start END AS Start,
   CASE WHEN b.Finish IS NOT NULL THEN b.Finish ELSE tm.Finish END AS Finish
FROM 
   TableMain tm
LEFT JOIN
   TableA a
ON
   a.DateVal = tm.DateVal
LEFT JOIN
   TableB b
ON
   b.DateVal = tm.DateVal
) t

The key here is that you use some left joins, a case statement, and a derived table result (in this case t). Please note my column names, simply change to what your column names are and this should work.