Friday, September 26, 2008

sql table join with a union of several tables

A join of a table with several other uniform tables can be done in a
single sql statement. In this example a table D (transactional data)
has to be joined with a table where products per transactions are
stored. It used to be a single table CTT_KPR. But recently a new line
of products was added as the separate table CTT_ZKKKPR. In order to
integrate data from the new table with the minimum efforts you can
use Union oprator and extend the existing join. The performance does
not change significantly. The code belows gives an idea how it can
look like.

......

AS D LEFT JOIN
( SELECT
ID_CTTMMT
,ID_MDW
,NUMTRA
,CODKPR
,CODMRK
,CODDVG
,DATPELEND
FROM KKC.CTT_KPR
UNION
SELECT
ID_CTTMMT
,ID_MDW
,NUMTRA
,CODKPR
,CODMRK
,CODDVG
,DATPELEND
FROM KKC.CTT_ZKKKPR
) as KPR
ON
D.ID_CTTMMT = KPR.ID_CTTMMT
AND D.NUMTRA = KPR.NUMTRA
AND D.ID_MDW = KPR.ID_MDW
.....

No comments: