Mapping Data from multiple table

Question

I built this union query to merge the code field to find which code is found in which table.

SQL=SELECT GOLD.code,1 AS Tbl1,0 AS Tbl2
FROM GOLD LEFT JOIN ADN ON GOLD.code = ADN.code
UNION
SELECT GOLD.code,0 AS Tbl1,1 AS Tbl2
FROM GOLD RIGHT JOIN ADN ON GOLD.code = ADN.code;

Here the results

Code          Tbl1 Tbl2
030?1975*007    0   1
030?1975*008    1   0
030 2259)000    1   0
Y031-1046-002x  1   0
031-1302-000    1   0
031-1303-000    1   0
031-1308-000    1   0
031-71013-RFX   1   0
04-035t:10-002  0   1
04-035t:10-003  1   0
04-035410-000   0   1
04-035410-000   1   0
04-035410-003   0   1
04-035410-003   1   0
047kjlkj**re    1   0
48              1   0

But I would like to get more compact like this where there is no duplicate because I will manipulate more that 500000 records par tables.

Code          Tbl1 Tbl2
030?1975*007    0   1
030?1975*008    1   0
030 2259)000    1   0
Y031-1046-002x  1   0
031-1302-000    1   0
031-1303-000    1   0
031-1308-000    1   0
031-71013-RFX   1   0
04-035t:10-002  0   1
04-035t:10-003  1   0
04-035410-000   1   1
04-035410-003   1   1
047kjlkj**re    1   0
48              1   0

or like that where tbl1 and tbl2 are merged into the mapping fields. This willbe the best.

Code         Mapping
030?1975*007    01
030?1975*008    10
030 2259)000    10
Y031-1046-002x  10
031-1302-000    10
031-1303-000    10
031-1308-000    10
031-71013-RFX   10
04-035t:10-002  01
04-035t:10-003  10
04-035410-000   11
04-035410-003   11
047kjlkj**re    10
48              10

Here the 2 tables

GOLD
Code
030?1975*008
030 2259)000
Y031-1046-002x
031-1302-000
031-1303-000
031-1308-000
031-71013-RFX
04-035t:10-003
04-035410-000
04-035410-003
047kjlkj**re

and

ADN
Code
030?1975*007
04-035t:10-002
04-035410-000
04-035410-003

xQbert xQbert · Accepted Answer · 2015-12-22T14:23:10

One approach is to wrap your query in a select getting only max from t1,t2 group by code.. This would be easier if access supported a full outer join.

SQL=
SELECT code, max(tbl1) as tbl1, max(tbl2) as tbl2, max(tbl1)&''&Max(tbl2) as stringversion
FROM (
SELECT GOLD.code,1 AS Tbl1,0 AS Tbl2 
FROM GOLD 
LEFT JOIN ADN 
  ON GOLD.code = ADN.code 
UNION 
SELECT GOLD.code,0 AS Tbl1,1 AS Tbl2 
FROM GOLD 
RIGHT JOIN ADN 
  ON GOLD.code = ADN.code) A
GROUP BY Code;

stringversion should combine the max of two columns together if that is the preferred output. I'm relying on some implicit casting though. by adding '' a string to a what appears to be a numeric datatype

This is commonly done in access by building a new query based on our existing one that simply aggregates the data.

Mapping Data from multiple table

1 Answers