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 |
alldaylong
Starting Member
6 Posts |
Posted - 2008-05-15 : 04:40:01
|
hi guys,i have 3 tables (T1, T2, T3), each with the same structure:ID1 -> char(10)ID2 -> char(12) NULLID3 -> char(10)Value1 -> moneyValue2 -> moneyValue3 -> moneyNote1 -> TextNote2 -> TextID1+ID2+ID3 is the clusterd unique key in each tablewhat i want:ID1, ID2, ID3 (with distinct occurencies of all 3 tables), T1.Value2, T2.Value2, T2.Value3, T3.Value1, T3.Note1 what i tried is to get all possible rows withSELECT ID1,ID2,ID3 FROM T1UNIONSELECT ID1,ID2,ID3 FROM T2UNIONSELECT ID1,ID2,ID3 FROM T3 but i dont know how to join or add the other columns. maybe withWITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ... any ideas?thx |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-15 : 09:56:50
|
Whats the relation between those three tables? based on what column values? or you just want all rows from both? |
|
|
alldaylong
Starting Member
6 Posts |
Posted - 2008-05-15 : 11:43:03
|
i want all distinct ID1+ID2+ID3 combinations of all three tablest1:2...5......82...NULL...7 t2:2...NULL...73...5......8 t3:emptyshould bring:2...5......82...NULL...73...5......8 this can be done with UNION, but i dont know how to link the other columns of the tables in one queryif i have in T1:ID1.....ID2.....ID3.....V1......V2......V3......Note12.......5.......8.......22......NULL....NULL....NULL3.......NULL....7.......14......NULL....NULL....NULL in T2:ID1.....ID2.....ID3.....V1......V2......V3......Note12.......7.......8.......11......66......NULL....NULL3.......NULL....7.......37......77......NULL....NULL in T3:ID1.....ID2.....ID3.....V1......V2......V3......Note12.......5.......8.......NULL....NULL....23......Here we go i want to get e.g.: ID1,ID2,ID3,T1.V1,T2.V2,T3.V3,T3.Note12.......5.......8.......22......NULL....23......Here we go3.......NULL....7.......14......77......NULL....NULL2.......7.......8.......NULL....66......NULL....NULL |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-15 : 12:08:16
|
[code]SELECT t.ID1,t.ID2,t.ID3,MAX(Value1),MAX(Value2),MAX(Value3),MAX(Text1),MAX(Text2),..FROM(SELECT ID1,ID2,ID3,Value1,Value2,Value3,Text1,Text2FROM table1 t1UNION ALLSELECT ID1,ID2,ID3,Value1,Value2,Value3,Text1,Text2FROM Table2 UNION ALLSELECT ID1,ID2,ID3,Value1,Value2,Value3,Text1,Text2FROM T3)tGROUP BY t.ID1,t.ID2,t.ID3[/code] |
|
|
alldaylong
Starting Member
6 Posts |
Posted - 2008-05-15 : 14:01:37
|
thx for your reply.i'm wondering of the output of your query for row 2 in table 1:3.......NULL....7.......37......77......NULL....NULL 37 would be the result for MAX(Value1)but i want to get the row from T1 - there should be 14... so MAX(..) won't work, right?i haven't had time to try your query .. it just came to my mind...even though i can't go for sure that there are NULL's in the rows... it's also possible, that there are lower or higher values... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-15 : 14:17:38
|
quote: Originally posted by alldaylong thx for your reply.i'm wondering of the output of your query for row 2 in table 1:3.......NULL....7.......37......77......NULL....NULL 37 would be the result for MAX(Value1)but i want to get the row from T1 - there should be 14... so MAX(..) won't work, right?i haven't had time to try your query .. it just came to my mind...even though i can't go for sure that there are NULL's in the rows... it's also possible, that there are lower or higher values...
I agree. In that case, what do you think the value you should be getting? lowest or highest value? What's your business requirement for that? You didnt specify it thats why i gave MAX() solution. |
|
|
alldaylong
Starting Member
6 Posts |
Posted - 2008-05-15 : 14:27:55
|
the value does not matter at all.i need the Value1 from T1, Value1 from T2, Value2 from T2, Value3 from T3 and Note1 from T3.i want all distinct rows for (ID1 + ID2 + ID3)= unique key of each table, but the number of rows in the tables varyif the (t1-t3) does not have a value for the key (row) it should bring a NULLis it possible to do this query, or should i split it up:first get all keys (possibilities) from the three tables with a UNION and then join the tables with the column i need ... or something like this??? which brings me to my first post: merge/join columns of 3 tables to a list of keys ID1+ID2+ID3 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-15 : 14:38:42
|
quote: Originally posted by alldaylong the value does not matter at all.i need the Value1 from T1, Value1 from T2, Value2 from T2, Value3 from T3 and Note1 from T3.i want all distinct rows for (ID1 + ID2 + ID3)= unique key of each table, but the number of rows in the tables varyif the (t1-t3) does not have a value for the key (row) it should bring a NULLis it possible to do this query, or should i split it up:first get all keys (possibilities) from the three tables with a UNION and then join the tables with the column i need ... or something like this??? which brings me to my first post: merge/join columns of 3 tables to a list of keys ID1+ID2+ID3
How can you get all the other values when you're taking unique combination of IDs? you can have only 2 optionns:-1. Either return any specific value for other column like lowest,highest,first non NULL,...or2. Return all the values from different rows as comma seperated onto single row for unique ID1,ID2,ID3 |
|
|
alldaylong
Starting Member
6 Posts |
Posted - 2008-05-16 : 02:34:20
|
maybe my thoughts are completely wrong, but what i'm doing is:T1 (costs2007):costcenter, order, account, realcosts, plannedcosts, plannotesT2 (costs2008):costcenter, order, account, realcosts, plannedcosts, plannotesT3 (costs2009) ... there will be a new table for every yearin one year there can be costcenter X, order Y and account Z... in the next year there can be costcenter A, order B and account C AND/OR/XOR costcenter X, order Y and account Zwhat i want to show in one datagrid (as columns) is:costcenter, order, account, costs2007.realcosts, costs2008.plannedcosts, costs2009.plannedcosts, costs2009.plannotes for all possibilities of "costcenter, order, account" - it's the unique key in each tablei hope it brings more light on the thing i want to do...p.s. i know it could be done in one table if i use one more column (year) - but as i get a huge amount of rows every year i decided to split it up. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-16 : 11:01:31
|
quote: Originally posted by alldaylong maybe my thoughts are completely wrong, but what i'm doing is:T1 (costs2007):costcenter, order, account, realcosts, plannedcosts, plannotesT2 (costs2008):costcenter, order, account, realcosts, plannedcosts, plannotesT3 (costs2009) ... there will be a new table for every yearin one year there can be costcenter X, order Y and account Z... in the next year there can be costcenter A, order B and account C AND/OR/XOR costcenter X, order Y and account Zwhat i want to show in one datagrid (as columns) is:costcenter, order, account, costs2007.realcosts, costs2008.plannedcosts, costs2009.plannedcosts, costs2009.plannotes for all possibilities of "costcenter, order, account" - it's the unique key in each tablei hope it brings more light on the thing i want to do...p.s. i know it could be done in one table if i use one more column (year) - but as i get a huge amount of rows every year i decided to split it up.
ok got now. This is what you wantSELECT t1.costcenter, t1.order, t1.account,t2.realcosts, t2.plannedcosts, t2.plannotes,t3.realcosts, t3.plannedcosts, t3.plannotes,t4.realcosts, t4.plannedcosts, t4.plannotesFROM(SELECT costcenter, order, account from costs2007UNIONSELECT costcenter, order, account from costs2008UNION SELECT costcenter, order, account from costs2009)t1LEFT JOIN costs2007 t2ON t2.costcenter = t1.costcenterAND t2.order = t1.orderAND t2.account = t1.accountLEFT JOIN costs2008 t3ON t3.costcenter = t1.costcenterAND t3.order = t1.orderAND t3.account = t1.accountLEFT JOIN costs2009 t4ON t4.costcenter = t1.costcenterAND t4.order = t1.orderAND t4.account = t1.account |
|
|
alldaylong
Starting Member
6 Posts |
Posted - 2008-05-16 : 15:47:33
|
looks very good now! i'll try this on tuesday, but i think it's exactly what i was looking for.thank you so much!!! |
|
|
|
|
|
|
|