CODEWITHSUNDEEP
sqlsql-serverjoin
benscammell
benscammell

Reputation: 27

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

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.

Upvotes: 0

Views: 147

Answers (4)

naota
naota

Reputation: 4718

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.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

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

Upvotes: 1

Vasan
Vasan

Reputation: 375

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)

Upvotes: 0

StanislavL
StanislavL

Reputation: 57381

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

Upvotes: 0

Related Questions