How do I join this... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How do I join this...

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2003-07-03 : 11:42:24

I have three table with the same table structure

Table1 has Dept, fund, account, code_A, code_B, Amount02

Table2 has Dept, fund, account, code_A, code_B, Amount03

Table3 has Dept, fund, account, code_A, code_B, Amount04

I need to join them into one table (FINAL_TABLE)with :
Dept, fund, account, code_A, code_B, Amount02, Amount03, Amount04
where I need to match fund, account, code_A, code_B from all three tables and also display records which might not have corresponding records in other two tables. I was assuming a FULL JOIN would work but that doesnot seem to give me the complete results.

Any help will appreciated.
PKS

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-03 : 12:13:20

CREATE TABLE TABLE1 (DEPT CHAR(2), FUND CHAR(2), CODEA CHAR(5), CODEB CHAR(5), AMOUNT02 MONEY)

CREATE TABLE TABLE2 (DEPT CHAR(2), FUND CHAR(2), CODEA CHAR(5), CODEB CHAR(5), AMOUNT03 MONEY)

CREATE TABLE TABLE3 (DEPT CHAR(2), FUND CHAR(2), CODEA CHAR(5), CODEB CHAR(5), AMOUNT04 MONEY)

INSERT INTO TABLE1 (DEPT , FUND , CODEA , CODEB , AMOUNT02 )
SELECT 'AA', 'XX', 'TRAN1', 'SSASS', 22.53
UNION ALL
SELECT 'BB', 'YY', 'TRAN9', 'SSASS', 152.32
UNION ALL
SELECT 'CC', 'ZZ', 'TRAN4', 'SSASS', 41.15

INSERT INTO TABLE2 (DEPT , FUND , CODEA , CODEB , AMOUNT03 )
SELECT 'AA', 'XX', 'TRAN1', 'SSASS', 1124.32
UNION ALL
SELECT 'FF', 'YY', 'TRAN9', 'SSASS', 1543.1
UNION ALL
SELECT 'CC', 'ZZ', 'TRAN4', 'SSASS',9.52

INSERT INTO TABLE3 (DEPT , FUND , CODEA , CODEB , AMOUNT04 )
SELECT 'AA', 'XX', 'TRAN1', 'SSASS', 112.68
UNION ALL
SELECT 'BB', 'YY', 'TRAN9', 'SSASS', 55874.52
UNION ALL
SELECT 'DD', 'ZZ', 'TRAN4', 'SSASS', 10.25

CREATE TABLE FINAL_TABLE (DEPT CHAR(2), FUND CHAR(2), CODEA CHAR(5), CODEB CHAR(5), AMOUNT02 MONEY, AMOUNT03 MONEY, AMOUNT04 MONEY)

INSERT INTO FINAL_TABLE (DEPT, FUND, CODEA, CODEB)
SELECT DEPT, FUND, CODEA, CODEB FROM TABLE1
UNION
SELECT DEPT, FUND, CODEA, CODEB FROM TABLE2
UNION
SELECT DEPT, FUND, CODEA, CODEB FROM TABLE3

UPDATE FINAL_TABLE
SET AMOUNT02 = A.AMOUNT02
FROM TABLE1 A
WHERE A.DEPT = FINAL_TABLE.DEPT
AND A.FUND = FINAL_TABLE.FUND
AND A.CODEA = FINAL_TABLE.CODEA
AND A.CODEB = FINAL_TABLE.CODEB

UPDATE FINAL_TABLE
SET AMOUNT03 = A.AMOUNT03
FROM TABLE2 A
WHERE A.DEPT = FINAL_TABLE.DEPT
AND A.FUND = FINAL_TABLE.FUND
AND A.CODEA = FINAL_TABLE.CODEA
AND A.CODEB = FINAL_TABLE.CODEB

UPDATE FINAL_TABLE
SET AMOUNT04 = A.AMOUNT04
FROM TABLE3 A
WHERE A.DEPT = FINAL_TABLE.DEPT
AND A.FUND = FINAL_TABLE.FUND
AND A.CODEA = FINAL_TABLE.CODEA
AND A.CODEB = FINAL_TABLE.CODEB

SELECT * FROM FINAL_TABLE

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-03 : 12:25:31

CREATE VIEW FinalTable

AS

SELECT dept, fund, acount, code_A, code_b, sum(amount_02) as Amount02, sum(Amount03) as Amount03, sum(Amount04) as Amount04
FROM
(
SELECT dept, fund, account, code_A, code_b, Amount02, 0 as Amount03, 0 as Amount04
FROM Table1
UNION ALL
SELECT dept, fund, account, code_A, code_b, 0 as Amount02, Amount03, 0 as Amount04
FROM Table2
UNION ALL
SELECT dept, fund, account, code_A, code_b, 0 as Amount02, 0 as Amount03, Amount04
FROM Table3
)
GROUP BY dept, fund, account, code_A, code_b

That will ensure that you have all the data you need. Avoid FULL OUTER JOINs. I have yet to see an situation in which a FULL OUTER JOIN's provides better result/performance then a UNION/GROUP BY solution.

Also: there is no reason to store this data in another table. Leave it as a VIEW. then you don't have to worry about updates -- the VIEW will always be up-to-date, and of course you can SELECT from it and use it just like a table.

- Jeff

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-03 : 12:49:18

Nicely done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources