Get unique common records with all records from both tables?

Question

Hi I have two tables structure is like this

Table 1

 Customer   Company   price    qty     item        invno
   1          a        89       8      item1        23 
   2          b        80       4      item2        22
   3          c        90       3      item1        45
   4          d        19       6      item3        12

table 2

 Customer   Company   price    qty     item       invno
   1          a        89       8      item1        23
   2          b        80       4      item2        18
   3          c        90       3      item1        45
   4          d        19       6      item3        15

basically table1 contains the current records and table2 current+past records and they both have other columns

what i want is get the all records from the table1 and table2 but in case of the duplication of invno i need that record from the table1 in this case resultset will contains the invno-23(table1),22(table1),45(table1),12(table1),18(table2),15(table2)

I tried using UNION but it gives different results on different column selection i stuck here any help would be great .

Answer 1

Here is one method, using union all and a filter:

select *
from table1 t1
union all
select *
from table2 t2
where not exists (select *
                  from table1 t1
                  where t1.invno = t2.invno
                 );

Answer 2

SELECT t2.Customer,
  t2.Company,
  t2.price,
  t2.qty,
  t2.item,
  IFNULL(t1.invno,t2.invno)
FROM table2      AS t2
LEFT JOIN table1 AS t1
ON t2.Customer=t1.Customer