CODEWITHSUNDEEP
mysql
Inkperial
Inkperial

Reputation: 83

UNION two tables with different columns, without duplicates, and update a field on union

I want to union two tables with the structure:

Table1: Id, Name, Reference1, Position

Table2: Id, Name, Reference2, ArtNr, Price, Position

Now to the difficult part: All fields with different "Reference" fields must be in that new table. The "Reference" field must be unique in the new table, so there won't be two items with the same "reference". BUT: If there's a record with the same reference in Table 1 and Table 2, the new Record must have the "Position" of Table 1. ALL Entries that are NOT in Table 1 but in Table 2, must have a new Position with the value of [MAX(Position) increment by 1] of Table 1.

I have NO IDEA, how to solve that :) Thanks for your help!

EDIT:

To union both:

SELECT Id
    ,NAME
    ,Reference
    ,NULL AS ArtNr
    ,NULL AS Price
    ,Position
FROM Table1

UNION

SELECT Id
    ,NAME
    ,Reference
    ,ArtNr
    ,Price
    ,Positionfrom Rechnung_Item
FROM
Table2

But that shows all entries of both tables...

Sample Data:

Table1:

Id Name Reference Position
1 Test 123 5
2 Test2 125 7

Table2:

Id Name Reference1 Position
1 Test 123 1
2 Test3 127 2

Desired output:

Id Name Reference2 Position
1 Test 123 5
2 Test2 125 7
3 Test3 127 9

Upvotes: 1

Views: 593

Answers (2)

Yashveer Singh
Yashveer Singh

Reputation: 1987

I tried creating sample data, it gives you result you required 

    declare  @temp1 as table (Id int , Name varchar(50), Reference int, Position int)
    insert into @temp1  (Id ,Name , Reference , Position ) values (1,'A',123,5)
    insert into @temp1  (Id ,Name , Reference , Position ) values (2,'B',125,7)
    --insert into @temp1  (Id ,Name , Reference , Position ) values (1,'C','Ref3',1)

    declare  @temp2 as table (Id int , Name varchar(50), Reference int, Position int, ArtNr int )
    insert into @temp2  (Id ,Name , Reference , Position,ArtNr ) values (1,'A',123,1,1)
    insert into @temp2  (Id ,Name , Reference , Position,ArtNr ) values (1,'C',127,2,2)
    --insert into @temp2  (Id ,Name , Reference , Position,ArtNr ) values (1,'B',128,1,3)
    --insert into @temp2  (Id ,Name , Reference , Position ,ArtNr) values (1,'C','Ref5',2,4)

    select isnull(r1  ,r2) as Reference ,

    ISNULL(n1,n2) as name , newPosition as Position 

     from (

    select   t1.Reference as r1 ,t2.Reference as r2 , t1.Name  as n1 , t2.Name as  n2 ,
    case when t1.Position is not null then t1.Position else (select max(Position)+ t2.Position from @temp1)  

    end as  newPosition

     from @temp1 t1 full outer join @temp2 t2 on t1.Reference = t2.Reference
     ) as tr

Upvotes: 2

Inkperial
Inkperial

Reputation: 83

I worked out that solution: 

SELECT * FROM (
SELECT Id, Name, Reference1 as Reference, Position FROM Table1
UNION ALL
SELECT Id, Name, Reference2 as Reference, Position + (SELECT MAX(Position) FROM Table1) FROM Table2) as result GROUP BY Reference

Upvotes: 1

Related Questions