How to intergrate 2 table

[cocoonwls]

Hi all, I am using SQL2005. I have 2 tables which are T1 and T2. I would like to intergrate them into one tabel, what i mean is add column when intergrate them instead of add row record.According what i know if using join or union will create a extra row.Record to be intergrate have to match the T1.AID with T2.ID Below is my senario.

Table : T1 Table : T2
=========================== ===================
AID | Code | Type | Amount1 ID | Code | Amount2
=========================== ===================
A 123 IA 200 B 223 12
B 223 IP 100 C 323 29
C 323 MR 55 D 444 30

Result have to get:

=====================================
AID | Code | Type | Amount1 | Amount2

A 123 IA 200 0
B 223 IP 100 12
C 323 MR 55 29

The record D in T2 would not include in new table. Any idea are welcome. Thanks in advance cocoonwls

[Kevin Horgan]

Hi, If you know that table T1 has more records than table T2 you could do a LEFT OUTER JOIN as follows:- SELECT t1.AID,t1.CODE,t1.TYPE,t1.AMOUNT1,COALESCE(t2.AMOUNT2,0) FROM t1 LEFT OUTER JOIN t2 on (t1.AID = t2.ID) I hope this helps. Cheers, Kevin

[cocoonwls]

Hi Kevin, I got it!thanks alot :laugh: I have edit my sql to match my case... thanks in advance cocoonwls

[cocoonwls]

Hi Kevin, I got it,Thanks alot! :laugh: I have success to use the left outer join in my case...Thanks again cocoonwls

[Kevin Horgan]

Hi Cocoonwis, You need to change the GROUP BY clause so it does not include the Amount fields. Try this instead... SELECT t1.AID, t1.CODE, MAX(distinct(t1.TYPE) as t1Type), SUM(t1.Amount) as t1Amount, COALESCE(SUM(t2.Amount),0) FROM t1 left outer join t2 on t1.AID = t2.ID group by t1.AID,t1.CODE Good luck, Kevin On Apr 9, 2009, at 9:17 AM, The Code Project forums wrote: Hi Kevin, Thanks for your help.I got it right now :) But i have another question about it, if the records are duplicated in t1, and also t2. How could i sum the amount in t1.Amount and t2.Amount. Example there are 2 record A1 in t1, then i would like to sum them.Also in t2, i would like to sum the amount which have the same id. I have try in my database, it dosen't SUM for me if i write like : SELECT t1.AID, t1.CODE, MAX(distinct(t1.TYPE) as t1Type), SUM(t1.Amount) as t1Amount, COALESCE(SUM(t2.Amount),0) FROM t1 left outer join t2 on t1.AID = t2.ID group by t1.AID,t1.CODE,t1.Amount,t2.Amount thanks in advance cocoonwls

[cocoonwls]

Hi kevin, First of all,thanks for your help:) I am facing another problem of the sql.How about if i need to filter also of the date?For example, in t1 have 3 record as below:

Table : T1 Table : T2
====================================== ===============================
AID | Code | Type | Amount1 | Date ID | Code | Amount2 | Date
====================================== ===============================
A 123 IA 200 1/15/2009 A 223 12 1/16/2009
B 223 IP 100 1/24/2009 B 323 29 2/13/2009
C 323 MR 55 2/11/2009 E 444 30 2/13/2009

If i want the data which are between 1/1 to 1/30. The result will be: Table : TableResult

==================================================
AID | Code | Type | Amount1 | Amount2 | Date

A 123 IA 200 12 1/15/2009
B 223 IP 100 0 1/24/2009

Note that the T2.ID for B is not include in. any ideas are welcome :doh: Thanks in advance regards cocoonwls

[Kevin Horgan]

Hi cocoonwls, You will need to expand the query to include a WHERE clause. So something like this for example... SELECT t1.AID, t1.CODE, MAX(distinct(t1.TYPE) as t1Type), SUM(t1.Amount) as t1Amount, COALESCE(SUM(t2.Amount),0) FROM t1 left outer join t2 on t1.AID = t2.ID WHERE t1.DATE >= '20090101' AND t1.DATE <= '20090130' group by t1.AID,t1.CODE Cheers, Kevin