SQL Server: Join 2 tables, preferring results from one table where there is a conflict

Question

I have tables that looks like this:-

tblConsuptionsFromA

id  meter        date        total
1      1      03/01/2014  100.1
2      1      04/01/2014  184.1
3      1      05/01/2014  134.1
4      1      06/01/2014  132.4
5      1      07/01/2014  126.1
6      1      08/01/2014  190.1

and...

tblConsuptionsFromB

id  meter        date        total
1      1      01/01/2014  164.1
2      1      02/01/2014  133.1
3      1      03/01/2014  136.1
4      1      04/01/2014  125.1
5      1      05/01/2014  190.1
6      1      06/01/2014  103.1
7      1      07/01/2014  164.1
8      1      08/01/2014  133.1
9      1      09/01/2014  136.1
10      1      10/01/2014  125.1
11      1      11/01/2014  190.1

I need to join these two tables, but if there is an entry for the same day in both table... only take the result from tblConsumptionsFromA.

So the result would be:-

id source_id  meter  from       date        total
1     1              1        B    01/01/2014  164.1
2     2              1        B    02/01/2014  133.1
3     1              1        A    03/01/2014  100.1
4     2              1        A    04/01/2014  184.1
5     3              1        A    05/01/2014  134.1
6     4              1        A    06/01/2014  132.4
7     5              1        A    07/01/2014  126.1
8     6              1        A    08/01/2014  190.1
9     9              1        B    09/01/2014  136.1
10    10             1        B    10/01/2014  125.1
11    11             1        B    11/01/2014  190.1

This is beyond me, so if someone can solve... I will be very impressed.

Answer 1

The UNION operator is used to combine the result-set of two or more SELECT statements.

 SELECT column_name(s) FROM table1
 UNION
 SELECT column_name(s) FROM table2;

The document of UNION is here: http://www.w3schools.com/sql/sql_union.asp

And ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

 ROW_NUMBER ( ) 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

The document of ROW_NUMBER() is here: http://technet.microsoft.com/en-us/library/ms186734.aspx

The following SQL statement uses UNION to select all records from the "tblConsuptionsFromA" and part of records from "tblConsuptionsFromB" tables.

SELECT ROW_NUMBER() OVER(ORDER BY DATE ASC) AS 'id', 
id AS 'source_id',meter, date,t AS 'from',total 
FROM(
    SELECT id,meter, date, 'A' AS t, total FROM tblConsuptionsFromA
UNION 
    SELECT id,meter, date, 'B' AS t,total FROM tblConsuptionsFromB
    WHERE NOT date IN (SELECT date FROM tblConsuptionsFromA) 
) AS C;

Hope this helps.

Answer 2

select ta.id, tb.id, ta.meter,
    if(ta.date is null, 'B', 'A') as from,
    if(ta.date is null, tb.date, ta.date) as date,
    if(ta.date is null, tb.total, ta.total) as total
from tblConsuptionsFromA ta
    full join tblConsuptionsFromB tb on ta.date=tb.date

Answer 3

You would need to do a Union of the 2 tables, and exclude records from tabletblConsuptionsFromB which are present in tblConsuptionsFromA, something like:

Select Id, Source_ID, meter, 'A' From, Date, Total 
  FROM tblConsuptionsFromA

Union All

Select Id, Source_ID, meter, 'B' From, Date, Total 
  FROM tblConsuptionsFromB
Where Date NOT EXISTS (Select Date from tblConsuptionsFromA)

Answer 4

Here's one way to do it:

SELECT
    COALESCE(a.source_id,b.source_id) as source_id,
    COALESCE(a.meter,b.meter) as meter,
    COALESCE(a.[from],b.[from]) as [from],
    COALESCE(a.[date],b.[date]) as [date],
    COALESCE(a.total,b.total)
FROM (select source_id,meter,'b' as [from],[date],total
       from tblConsuptionsFromB) b
         left join
     (select source_id,meter,'a' as [from],[date],total
       from tblConsuptionsFromA) a
         on
            a.meter = b.meter and
            a.[date] = b.[date]

Unfortunately, there's no shorthand like COALESCE(a.*,b.*) to apply the COALESCE to all columns