| Author |
 Topic  |
|
|
alldaylong
Starting Member
6 Posts |
 Posted - 05/15/2008 : 04:40:01
|
hi guys,
i have 3 tables (T1, T2, T3), each with the same structure:
ID1 -> char(10)
ID2 -> char(12) NULL
ID3 -> char(10)
Value1 -> money
Value2 -> money
Value3 -> money
Note1 -> Text
Note2 -> Text
ID1+ID2+ID3 is the clusterd unique key in each table
what 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 with
SELECT ID1,ID2,ID3 FROM T1
UNION
SELECT ID1,ID2,ID3 FROM T2
UNION
SELECT ID1,ID2,ID3 FROM T3
but i dont know how to join or add the other columns. maybe with
WITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ...
any ideas?
thx |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 05/15/2008 : 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 - 05/15/2008 : 11:43:03
|
i want all distinct ID1+ID2+ID3 combinations of all three tables
t1:
2...5......8
2...NULL...7
t2:
2...NULL...7
3...5......8
t3:
empty
should bring:
2...5......8
2...NULL...7
3...5......8
this can be done with UNION, but i dont know how to link the other columns of the tables in one query
if i have in T1:
ID1.....ID2.....ID3.....V1......V2......V3......Note1
2.......5.......8.......22......NULL....NULL....NULL
3.......NULL....7.......14......NULL....NULL....NULL
in T2:
ID1.....ID2.....ID3.....V1......V2......V3......Note1
2.......7.......8.......11......66......NULL....NULL
3.......NULL....7.......37......77......NULL....NULL
in T3:
ID1.....ID2.....ID3.....V1......V2......V3......Note1
2.......5.......8.......NULL....NULL....23......Here we go
i want to get e.g.: ID1,ID2,ID3,T1.V1,T2.V2,T3.V3,T3.Note1
2.......5.......8.......22......NULL....23......Here we go
3.......NULL....7.......14......77......NULL....NULL
2.......7.......8.......NULL....66......NULL....NULL
|
Edited by - alldaylong on 05/15/2008 11:55:49 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 05/15/2008 : 12:08:16
|
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,Text2
FROM table1 t1
UNION ALL
SELECT ID1,ID2,ID3,
Value1,Value2,Value3,
Text1,Text2
FROM Table2
UNION ALL
SELECT ID1,ID2,ID3,
Value1,Value2,Value3,
Text1,Text2
FROM T3)t
GROUP BY t.ID1,t.ID2,t.ID3
|
Edited by - visakh16 on 05/15/2008 12:12:22 |
|
|
|
alldaylong
Starting Member
6 Posts |
Posted - 05/15/2008 : 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... |
Edited by - alldaylong on 05/15/2008 14:04:04 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 05/15/2008 : 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 - 05/15/2008 : 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 vary
if the (t1-t3) does not have a value for the key (row) it should bring a NULL
is 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
India
52326 Posts |
Posted - 05/15/2008 : 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 vary
if the (t1-t3) does not have a value for the key (row) it should bring a NULL
is 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,...
or
2. Return all the values from different rows as comma seperated onto single row for unique ID1,ID2,ID3 |
|
|
|
alldaylong
Starting Member
6 Posts |
Posted - 05/16/2008 : 02:34:20
|
maybe my thoughts are completely wrong, but what i'm doing is:
T1 (costs2007):
costcenter, order, account, realcosts, plannedcosts, plannotes
T2 (costs2008):
costcenter, order, account, realcosts, plannedcosts, plannotes
T3 (costs2009) ... there will be a new table for every year
in 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 Z
what 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 table
i 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
India
52326 Posts |
Posted - 05/16/2008 : 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, plannotes
T2 (costs2008):
costcenter, order, account, realcosts, plannedcosts, plannotes
T3 (costs2009) ... there will be a new table for every year
in 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 Z
what 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 table
i 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 want
SELECT t1.costcenter, t1.order, t1.account,
t2.realcosts, t2.plannedcosts, t2.plannotes,
t3.realcosts, t3.plannedcosts, t3.plannotes,
t4.realcosts, t4.plannedcosts, t4.plannotes
FROM
(SELECT costcenter, order, account from costs2007
UNION
SELECT costcenter, order, account from costs2008
UNION
SELECT costcenter, order, account from costs2009
)t1
LEFT JOIN costs2007 t2
ON t2.costcenter = t1.costcenter
AND t2.order = t1.order
AND t2.account = t1.account
LEFT JOIN costs2008 t3
ON t3.costcenter = t1.costcenter
AND t3.order = t1.order
AND t3.account = t1.account
LEFT JOIN costs2009 t4
ON t4.costcenter = t1.costcenter
AND t4.order = t1.order
AND t4.account = t1.account |
|
|
|
alldaylong
Starting Member
6 Posts |
Posted - 05/16/2008 : 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!!! |
|
|
| |
Topic |
|
|
|
Merge tables on unique key using different columns