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
 how to join a table to itself?

Author  Topic 

aomar
Starting Member

6 Posts

Posted - 2008-04-23 : 20:35:54
how to join a table to itself to get all possible compinations
using join

consider
select tab1.col col1,tab2.col col2
from tab tab1,tab tab2

i want to make the same thing using join

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-23 : 21:14:00
Use CROSS JOIN

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-12 : 02:39:07
Hi,

select t1.col as col1 , t2.col as col2
from tab as t1
inner join tab as t2 on t1.col = t2.col

Be cool..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 03:41:32
quote:
Originally posted by raky

Hi,

select t1.col as col1 , t2.col as col2
from tab as t1
inner join tab as t2 on t1.col = t2.col

Be cool..


This wont return all possible combinations as OP requested. this will return only records based on matching condition t1.col = t2.col. You should use CROSS JOIN as suggested by jsmith to get all possible combinations.
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-12 : 03:53:11
quote:
Originally posted by raky

Hi,

select t1.col as col1 , t2.col as col2
from tab as t1
inner join tab as t2 on t1.col = t2.col

Be cool..


All I can say to that is.. Wow, where did you learn SQL?

Why not just do select col from tab as it will return the same?

Use Cross Join as stated already.
Go to Top of Page

pootle_flump

1064 Posts

Posted - 2008-05-12 : 05:24:35
quote:
Originally posted by RickD

All I can say to that is.. Wow, where did you learn SQL?

Why not just do select col from tab as it will return the same?
Only if there are no nulls, the relationship is one to one and the data in both tables is duplicated.
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-12 : 06:11:31
quote:
Originally posted by pootle_flump

quote:
Originally posted by RickD

All I can say to that is.. Wow, where did you learn SQL?

Why not just do select col from tab as it will return the same?
Only if there are no nulls, the relationship is one to one and the data in both tables is duplicated.


The ralationship when joining to the same table on the same column would always be one to one surely?!?
Go to Top of Page

pootle_flump

1064 Posts

Posted - 2008-05-12 : 06:37:56
Duh. Still off the caffeine. Still suffering. NULLS still an issue though
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-12 : 06:42:36
lol, yeah, they would be, but I really think that the suggestion was a bad idea in the first place and not something you would really want to attempt.
Go to Top of Page
   

- Advertisement -