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 .

UNION should be doing what you want. Of course you would get different results when different columns are being used. UNION compares the matching columns (all columns must match by number and type). What is your actual question? — Mike Cheel, Dec 05 '13 at 15:48
Actually i want all the rows from table1 and non repeating rows from table2 in case of repeatation i want that record from table1 but i cant apply the `Select * ` and then do the UNION beacause both tables contains the near about 60 columns and there are two text fields so union doesnt work as `Test data type can not apply the distinct` so i want somthing that will get all rows without depending on which columns i select — , Dec 06 '13 at 05:08

score 0 · Answer 1 · edited Dec 05 '13 at 15:50

0

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

edited Dec 05 '13 at 15:50

Grambot

4,370
5
28
43

answered Dec 05 '13 at 15:48

mameyugo

298
1
6

score 0 · Accepted Answer · answered Dec 05 '13 at 15:48

0

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
                 );

answered Dec 05 '13 at 15:48

Gordon Linoff

1,242,037
58
646
786

Thanks it worked ! my stupidity this simple didn't think of it. – Dec 06 '13 at 05:26

Get unique common records with all records from both tables?

2 Answers2