Merge tables

[Whitsant]

I have a very simple question which is anoying me already for a very long time. I have two tables both with a key field, a common field and a table specic field. Like :

Key,common,spec1
222,AAAA,20000
333,BBBB,0000
444,CCCC,22000

Key,common,spec2
111,EEEE,PRO
222,AAAA,PRV
777,FFFF,PSR

Which I want to merge to one table like this :

Key,common,spec1,spec2
111,EEEE,-----,PRO
222,AAAA,20000,PRV
333,BBBB,10000,---
444,CCCC,22000,---
777,EEEE,----- ,PSR

If I set a relation on the keyfield, where both keys should be equal, the result is :

222,AAAA,20000,PRV

If I set a relation on the keyfield, wiith all records from table 1 and from table 2 where both keys are equal, the result is :

222,AAAA,20000,PRV
333,BBBB,10000,---
444,CCCC,22000,---

If I set a relation on the keyfield, with all records from table 2 and from table 1 where both keys are equal, the result is :

111,EEEE,-----,PRO
222,AAAA,20000,PRV
777,EEEE,----- ,PSR

How do I solve this?

 

[cuttsy]

instead of merging the tables i think you should split them:

111,EEEE
222,AAAA
333,BBBB
444,CCCC
777,FFFF

444,22000
222,20000
333,10000

111,PRO
222,PRV
777,PSR

 

[Whitsant]

I see what you meen Cuttsy, but I still need that one table (to export to Excel).

Thanks for your reply.

 

[cuttsy]

Do a quey and export the query to excel.

 

[Whitsant]

That's exactly my problem : I don't know how to make the query.

 

[cuttsy]

SELECT Table1.Key, Table1.Common, Table1.Spec1, Table2.Spec2
FROM Table1 INNER JOIN Table2 ON Table1.Key = Table2.Key

 

[stopher]

...Which I want to merge to one table like this :

Key,common,spec1,spec2
111,EEEE,-----,PRO
222,AAAA,20000,PRV
333,BBBB,10000,---
444,CCCC,22000,---
777,EEEE,----- ,PSR

....How do I solve this?

Try this:

SELECT myAlias2.key, myAlias2.common, table1.spec1, table2.spec2
FROM 
    ((SELECT DISTINCT key, common FROM 
              (SELECT key, common
               FROM Table1
               union
               SELECT key, common
               FROM Table2) 
              AS myAlias1) 
     AS myAlias2 
LEFT JOIN table1 ON myAlias2.key = table1.key) 
LEFT JOIN table2 ON myAlias2.key = table2.key;

I've assumed your tables are called table1 and table2 respectively so you need to changed to your table names.
hth
Stopher