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]

@Rabbit
Oh Sorry I’d forgotten to explain that, MyTable 1 have some other Columns too, and with them makes primary key(like Id_Code+IdCountry) 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]

@Rabbit
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]

@Rabbit
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?