SELECT ID1,ID2,ID3 FROM T1
UNION
SELECT ID1,ID2,ID3 FROM T2
UNION
SELECT ID1,ID2,ID3 FROM T3

WITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ...

2...5......8
2...NULL...7

2...NULL...7
3...5......8

2...5......8
2...NULL...7
3...5......8

ID1.....ID2.....ID3.....V1......V2......V3......Note1
2.......5.......8.......22......NULL....NULL....NULL
3.......NULL....7.......14......NULL....NULL....NULL

ID1.....ID2.....ID3.....V1......V2......V3......Note1
2.......7.......8.......11......66......NULL....NULL
3.......NULL....7.......37......77......NULL....NULL

ID1.....ID2.....ID3.....V1......V2......V3......Note1
2.......5.......8.......NULL....NULL....23......Here we go

2.......5.......8.......22......NULL....23......Here we go
3.......NULL....7.......14......77......NULL....NULL
2.......7.......8.......NULL....66......NULL....NULL

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

3.......NULL....7.......37......77......NULL....NULL

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...

---

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

---

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.

---

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