Join Unrelated tables - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join Unrelated tables

Author

Topic

sneupane
Starting Member

28 Posts

Posted - 2010-05-07 : 09:45:53

I have two tables with no common key.

Table1
P Qt Rt
a 15 48
b 16 50
c 20 39
a 15 32
c 21 31

Table 2
Numb Ut
Num1 26
Num2 27
Num3 28
Num2 62

Result I need

P Qt Rt Numb Ut
a 30 80 Num1 26
b 16 50 Num2 89
c 41 70 Num3 28

I tried to join it even though it does not have common key in the following way.

select

P
,sum(Qt)
,sum(Rt)
,Numb
,sum(Ut)

From Table1,Table2
Group By P,Numb

The query was run but gave me very wrong results.I tried union too it did not work.Could anyone help me in this error?

SAROJ

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 10:31:42

I don't know what purpose it will serve you.

I just did it for fun.

Declare @Table1 table
(p varchar(10),
QT int,
RT int
)

Declare @Table2 table
(
Numb varchar(10),
Ut int
)

Insert into @Table1
Select 'a', 15 ,48 union
Select 'b', 16 ,50 union
Select 'c', 20 ,39 union
Select 'a', 15 ,32 union
Select 'c', 21 ,31

Insert into @Table2
Select 'Num1', 26 union
Select 'Num2', 27 union
Select 'Num3', 28 union
Select 'Num2', 62

Select T1.p, T1.qt, T1.rt, T2.Numb, T2.Ut
from
(
Select Rank() over (order by Numb) as Srno, Numb , sum(Ut) Ut from @Table2 group by Numb) As T2
Join
(Select Rank() over (order by P) as Srno, P , sum(qt) QT , sum(rt) Rt from @Table1 group by P) As T1
on T1.Srno = T2.Srno

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-07 : 13:35:40

i cant understand how you make sure the columns get joined based on order in which they're given above. I think you're assuming that values will always be in sequential order as per sample data which i dont think will always be true. please note that unless you dont have a column with which you specify order you cant guarantee the order of retrieval from each table and joining.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 13:41:55

quote:
Originally posted by visakh16

I think you're assuming that values will always be in sequential order as per sample data which i dont think will always be true. please note that unless you dont have a column with which you specify order you cant guarantee the order of retrieval from each table and joining.

Hi Visakh,

I totally agree with you that unless you a proper joining column the data may be incorrect..

As i said in my earlier post, I just did it for fun..

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-07 : 13:44:13

oh ok. just told that it wont work always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources