Attach 2 Tables (Complex Joins)

[Mahdis]

Hi,

I have a small problem of the following Tables, I have MyTable1 (for example) and I want to attach content of following MyTable2 to MyTable1, but MyTable2 have the Data just 1 week ago, and it should be in appropriate columns against that columns in MyTable1, pl tel me what can I do?


MyTable 1 :

Id_Date Id_Branch NewBalance
07/08/2012 1270 160000
07/08/2012 1654 170000
07/08/2012 3046 160000
---------------------------------------
11/08/2012 2046 140000
11/08/2012 4395 170000
11/08/2012 1720 160000

MyTable 2 :

Id_Date OldBalance
18/08/2012 140000
18/08/2012 170000
18/08/2012 160000

It should be some thing like this(MyTable 3) :


Id_Date Id_Branch NewBalance OldBalance
07/08/2012 1270 160000 -
07/08/2012 1654 170000 -
07/08/2012 3046 160000 -
---------------------------------------------------
11/08/2012 2046 140000 160000
11/08/2012 4395 170000 170000
11/08/2012 1720 160000 160000
---------------------------------------------------
18/08/2012 3046 110000 140000
18/08/2012 3046 160000 170000
18/08/2012 3046 150000 160000


Thanks a lot.....

[Rabbit]

You have duplicate dates. How do you know which one to join to?

[Mahdis]

Attach 2 Tables (Complex Joins)

You have duplicate dates. How do you know which one to join to?

Oh Sorry I’d forgotten to explain that, MyTable 1 have some other Columns too, and with them makes primary key(like Id_Code+IdCount ry) and also, weekly, new data is added to and NewBalance is changed to OldBalance for new week, so I should Show OldBalance, next to the Id_date, Id_Branch, Id_Code, IdCountry and NewBalance to GridView in asp.net

[Rabbit]

So table 1 has a primary key composed of id_code and idcountry. Does table 2 have the same thing?

If so, outer join the two tables together and you will get the result you want.

[Mahdis]

Attach 2 Tables (Complex Joins)

So table 1 has a primary key composed of id_code and idcountry. Does table 2 have the same thing?

If so, outer join the two tables together and you will get the result you want.

No, as a matter of fact I should say MyTable 2 comes out of the MyTAble 1 after every week, that means, after 1 week, actually I have new data like third Table as shown(MyTable3) , that should be added to end of MyTable 1(for example named MyTable 2) and NewBalance should be changed to OldBalance with new rows, it means in real I haven’t MyTable2 and when I’d wanted to solve it I’d supposed that (maybe it is wrong because I ‘m beginner in Sql Server)


MyTable 1 :

Id_Date Id_Branch NewBalance
--------------------------------------
07/08/2012 1270 160000
07/08/2012 1654 170000
07/08/2012 3046 160000
---------------------------------------
11/08/2012 2046 140000
11/08/2012 4395 170000
11/08/2012 1720 160000

MyTable 2(New Week) :

Id_Date OldBalance
--------------------------------
18/08/2012 140000
18/08/2012 170000
18/08/2012 160000

It should be some thing like this(MyTable 3) :


Id_Date Id_Branch NewBalance OldBalance
---------------------------------------------------
07/08/2012 1270 160000 -
07/08/2012 1654 170000 -
07/08/2012 3046 160000 -
---------------------------------------------------
11/08/2012 2046 140000 160000
11/08/2012 4395 170000 170000
11/08/2012 1720 160000 160000
---------------------------------------------------
18/08/2012 3046 110000 140000
18/08/2012 3046 160000 170000
18/08/2012 3046 150000 160000

[Rabbit]

I have no idea what you just said.

[Mahdis]

Attach 2 Tables (Complex Joins)

I have no idea what you just said.

So When I have a table that every week should be updated in part of table (just some columns) that results from 1 week ago, so what should I do, could u pl just tell me that? Thank u so much.....


Id_date Id_Branch NewBalance($) OldBalance($)
07/08/2012 2014 140000
07/08/2012 1354 120000
07/08/2012 1014 170000
---------------------------------------------------------
11/08/2012 2014 180000 140000
11/08/2012 1354 110000 120000
11/08/2012 1014 150000 170000
---------------------------------------------------------
18/08/2012 2014 170000 180000
18/08/2012 1354 130000 110000
18/08/2012 1014 190000 150000

[ck9663]

Are Id_Branch and Id_Date unique? Do you have the dates exactly a week apart even across different months?


~~ CK

[Rabbit]

In addition to those questions, is each new file always linked to the most recent date in the table?