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.
| Author |
Topic |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2002-02-21 : 02:10:45
|
| hi guys.. back again..i have 3 table mon1 ,mon2 mon3 same structure with diffrent data..i need to join 3 tables and get all the rows in 3 tables... the linking field is account_id,rate_rankmon1Account_id , rate_rank , usg_qty 1245 0 10 1245 1 35 1245 2 15 1122 1 20 mon2Account_id , rate_rank , usg_qty 1245 0 10 1245 1 20 1122 0 10 1122 1 25 mon2Account_id , rate_rank , usg_qty 1245 0 10 1122 0 10 the result should be look like....Acct_id,m1_rank , m1_qty,m2_rank , m2_qty,m3_rank , m3_qty, 1245 0 10 0 10 0 10 1245 1 35 1 20 null null 1245 2 15 null null null null 1122 1 20 0 10 0 10 1122 null null 1 25 *m1 mon1 *m2 mon2 *m3 mon3 the query should return all the account of mon1 table and respective mon2 and mon3... but the rate_rank should be from all the tables..thanks in advance....======================================Ask to your self before u ask someone |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-21 : 02:45:55
|
| select m1.AccountID,m1.qty,m2.rank , m2.qty,m3.rank , m3.qtyfrom m1 left outer joinm2on m1.account_id =m2.Account_id and m1.rank=m2.rankleft outer joinm3on m1.account_id=m3.account_id and m1.rank=m3.rankHTH-------------------------------------------------------------- |
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-02-21 : 03:06:08
|
| thanks.. i tried this but it dont work... when i link the mon1.rate_rank =mon2.rate_rank..we have a excess row in mon2...acct rank qty 1122 1 25 this row will be missing...the base for all 3 tables is mon1 account id.and it should all the rate_rank in mon1.. mon2 ..mon3======================================Ask to your self before u ask someone |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-21 : 03:25:54
|
| Can you put some light on the information of this tables. How are they linked and what information do they contain.Will table m3 contains not contain duplicates of Account_id and will there be mismatch of rank_id's in table m1 and m2?. if this is false then this query should help you out.select m1.AccountID,m1.qty,m2.rank , m2.qty,m3.rank , m3.qtyfrom m1 left outer joinm2on m1.account_id =m2.Account_id and m1.rank=m2.rankleft outer joinm3on m1.account_id=m3.account_id --------------------------------------------------------------Edited by - Nazim on 02/21/2002 03:32:28 |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-02-21 : 03:36:15
|
| this is a telecome project....every table will have unique record account_id,rank combination...basically it is a 3 months data of usage...the common field is account_id using left join mon1... and we can link the rate_rank too but it should be full join so that we can get all the rate_rank in 3 tables...thanks======================================Ask to your self before u ask someone |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-02-21 : 04:49:13
|
| thanks nazim i got the solutions... full join 3 table on rate_rank and mon1.account_id=mon2.account_id andmon1.account_id=mon3.account_idit works.....bye======================================Ask to your self before u ask someone |
|
|
|
|
|
|
|
|
Full outer Joins