Union of two tables but show which table the data

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.

Answer 1

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

Answer 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

Answer 3

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;

Answer 4

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)

Answer 5

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