SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Merge tables on unique key using different columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alldaylong
Starting Member

6 Posts

Posted - 05/15/2008 :  04:40:01  Show Profile  Reply with Quote
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
52309 Posts

Posted - 05/15/2008 :  09:56:50  Show Profile  Reply with Quote
Whats the relation between those three tables? based on what column values? or you just want all rows from both?
Go to Top of Page

alldaylong
Starting Member

6 Posts

Posted - 05/15/2008 :  11:43:03  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/15/2008 :  12:08:16  Show Profile  Reply with Quote
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
Go to Top of Page

alldaylong
Starting Member

6 Posts

Posted - 05/15/2008 :  14:01:37  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/15/2008 :  14:17:38  Show Profile  Reply with Quote
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.
Go to Top of Page

alldaylong
Starting Member

6 Posts

Posted - 05/15/2008 :  14:27:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/15/2008 :  14:38:42  Show Profile  Reply with Quote
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
Go to Top of Page

alldaylong
Starting Member

6 Posts

Posted - 05/16/2008 :  02:34:20  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/16/2008 :  11:01:31  Show Profile  Reply with Quote
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
Go to Top of Page

alldaylong
Starting Member

6 Posts

Posted - 05/16/2008 :  15:47:33  Show Profile  Reply with Quote
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!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000