Union of two tables but show which table the data came from

Question

I have two tables:

           TABLE_A                 TABLE_B
Fields:    Trans   Amend           Trans   Amend 
data:      100       0             100      0
           100       1
           110       0         
                                   120      0
                                   120      1
           130       0             130      0 
                                   130      1
           140       0             140      0
           150       0             150      0
           150       1             150      1
           150       2

What I want is a table (view) that will combine (union) these to tables but will only show the highest Amend for each Trans

Looking for this as the answer:

Fields:    Trans   Amend   
data:      100       1
           110       0
           120       1
           130       1
           140       0 
           150       2

Then to make it harder, I would like to know if there is a way I can tell from which table the data is coming from. Table A always wins when Record A and Record B are equal Looking for this as the answer:

Fields:    Trans   Amend    WhichTBL
data:      100       1      Table_A
           110       0      Table_A
           120       1      Table_B
           130       1      Table_B
           140       0      Table_A
           150       2      Table_A

I know a UNION can't be done to get this result.

The accepted answer may return wrong results. The solution of Carlos A. Ibarra should be used instead. — Lucero, Aug 29 '12 at 20:07
I've updated my answer after actually working a solution out. Plz reconsider. — Beth, Aug 29 '12 at 20:27

score 2 · Answer 1 · edited Jan 23 '19 at 19:53

2

What if you added a string in your select and aliased it as a column?

SELECT Trans, Amend, 'Table_A' as WhichTBL
FROM (your 1st select query)
UNION
SELECT Trans, Amend, 'Table_B' as WhichTBL
FROM (your 2nd select query)
ORDER BY Trans

edited Jan 23 '19 at 19:53

answered Jan 23 '19 at 17:35

Lubomir Ivanov

21
2

Carlos A. Ibarra · Answer 2 · 2012-08-29T20:00:22.747

1

In Teradata SQL you would do the following, not sure about SQL Server:

select trans,amend,WhichTBL from
(
select trans,amend,'Table_A' WhichTBL from Table_A
union
select trans,amend,'Table_B' WhichTBL from Table_B
) X
qualify row_number() over(partition by trans order by amend desc, WhichTBL) = 1
order by trans;

A version using Lucero's suggestion if your SQL doesn't have a QUALIFY clause:

select trans,amend,WhichTBL from
(
   select x.*,row_number() over(partition by trans order by amend desc, WhichTBL) as rn
   (
      select trans,amend,'Table_A' as WhichTBL from Table_A
      union
      select trans,amend,'Table_B' as WhichTBL from Table_B
   ) Derived1 as X
) Derived2
where rn = 1
order by trans;

edited Aug 29 '12 at 20:00

answered Aug 29 '12 at 18:55

Carlos A. Ibarra

6,002
1
28
38

The `QUALIFY` clause doesn't exist in all DB systems, but it's easy to simulate by using a nested query exposing the `ROW_NUMBER()` and then filter this query with a `WHERE` clause. – Lucero Aug 29 '12 at 19:31
This should be marked as the correct answer. However, it would be better to change the `union` to `union all` (since rows are never the same there is no point in de-duplicating them). – Lucero Aug 29 '12 at 20:01

Beth · Accepted Answer · 2012-08-29T20:24:34.233

would this work?

SELECT 
    trans, MAX(max_amend) as max_max_amend
FROM
    (SELECT
        'a' AS src, trans, MAX(amend) AS max_amend
    FROM
        table_a
    GROUP BY
        trans

    UNION ALL

    SELECT
        'b' AS src, trans, MAX(amend) AS max_amend
    FROM
        table_b
    GROUP BY
        trans) m
GROUP BY
    trans

Lucero's point below is correct, the min(src) would be on the global set, not the related max()

I think you'd have to combine the source and table values into one column you can max. In your example, adding 1 to the value is all you need to distinguish the sources, like:

SELECT trans, Max(amend) AS MaxOfamend, 1+[amend] AS isa, 0 AS isb
FROM TableA
GROUP BY trans

but you could add 100, or multiply by a big value, or whatever works with your data. The idea is to combine the two pieces of information, the amend value and the source, into one column.

Then, after the information is combined, you get the max of that value, then strip off the source flag by uncombining them (subtracting 1, dividing by 100, whatever)

OK, here's what I got:

CREATE VIEW [dbo].[viewA]    AS
SELECT trans, MAX(amend + .20) AS srcIsA, 0 AS srcIsb
FROM  dbo.tableA
GROUP BY trans

CREATE VIEW [dbo].[viewB]    AS
SELECT trans, 0 AS srcIsA, MAX(amend + .10) AS srcIsB
FROM  dbo.tableB
GROUP BY trans

CREATE VIEW [dbo].[viewU]    AS
SELECT * from viewA
union all
select *
FROM  viewb

CREATE VIEW [dbo].[viewv]    AS
SELECT trans, srcIsA, srcIsb, srcIsA + srcIsb AS total
FROM  dbo.viewU

CREATE VIEW [dbo].[vieww]    AS
SELECT trans, MAX(total) AS max_total
FROM  dbo.viewv
GROUP BY trans

CREATE VIEW [dbo].[viewx] AS
SELECT trans, 
max_total, 
CAST(max_total AS int) AS maxval, 
CASE WHEN (max_total - CAST(max_total AS int)) = .1 THEN 'a' ELSE 'b' END AS src
FROM  dbo.vieww

`GROUP BY` doesn't cut it - the `MAX(max_amend)` might come from a different row (and thereby a different source table) than `MIN(src)` - so that the (true) origin of the row would be lost (e.g. you would attribute the trans 130 in the example with amend 1 to table A). — Lucero, Aug 29 '12 at 19:29

Gordon Linoff · Answer 4 · 2012-08-29T19:43:55.577

0

I want to offer that you can do this with a join and aggregation, using standard SQL:

select coalesce(a.trans, b.trans) as trans,
       (case when coalesce(max(b.amend), -1) > coalesce(max(a.amend), -1)
             then max(b.amend)
             else max(a.amend)
        end) as amend,
       (case when coalesce(max(b.amend), -1) > coalesce(max(a.amend) , -1)
             then 'B' else 'A'
        end) as whichTable
from Table_A a full outer join
     Table_B b
     on a.trans = b.trans
group by coalesce(a.trans, b.trans)

edited Aug 29 '12 at 19:43

answered Aug 29 '12 at 19:00

Gordon Linoff

1,242,037
58
646
786

Buggy: Whenever `a.amend` is NULL (caused by the full outer join only finding a `b.trans`) your `MAX(a.amend)` will also be NULL, and therefore the condition `max(b.amend) > max(a.amend)` will return false, which would then pick the "A" table value and identifier despite it being NULL (oh, and also note the syntax error on the `'A"` string literal). – Lucero Aug 29 '12 at 19:38
@Lucero . . To be precise, max(a.amend) or max(b.amend) would need to be NULL for a failure to happen (the slight difference being when amend is NULL in the data). In any case, I fixed the problem, assuming that amend is always non-negative in the data (as it is in the example data). – Gordon Linoff Aug 29 '12 at 19:44
in the case of `max(b.amend)` being NULL it would have (unintentionally?) worked because the `ELSE` part of the case would be the correct one to be used - which is why I only pointed out the other one. – Lucero Aug 29 '12 at 20:00

user1619962 · Answer 5 · 2012-08-29T20:39:24.477

If Amend only has value 1 and 0 then the first question can be done

select Trans,sum(Amend) AmendMax from (select Trans,Amend from TABLE_A
union select Trans,Amend from TABLE_B) C group by Trans

the secound question would be

select Trans,max(Amend) Amend,case when sum(s)=1 or  sum(s)=2 or  sum(s)=21 
then 'Table-A' when sum(s)=10 or  sum(s)=12 or sum(s)=20  then 'Table-B' 
when sum(s)=11 or  sum(s)=22 then 'Table-A and B' end s from 
(select case when max(Amend)=1 then 1 else 2 end s,Trans,max(Amend) Amend from TABLE_A 
group by Trans union select case when max(Amend)=1 
then 10 else 20 end s,Trans,max(Amend) Amend from TABLE_B group by Trans) C group by Trans

Union of two tables but show which table the data came from

5 Answers5

Lucero's point below is correct, the min(src) would be on the global set, not the related max()

Related