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 compinationsusing joinconsiderselect tab1.col col1,tab2.col col2from tab tab1,tab tab2i want to make the same thing using join |
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2008-05-12 : 02:39:07
|
Hi,select t1.col as col1 , t2.col as col2from tab as t1inner join tab as t2 on t1.col = t2.colBe cool.. |
|
|
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 col2from tab as t1inner join tab as t2 on t1.col = t2.colBe 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. |
|
|
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 col2from tab as t1inner join tab as t2 on t1.col = t2.colBe 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. |
|
|
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. |
|
|
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?!? |
|
|
pootle_flump
1064 Posts |
Posted - 2008-05-12 : 06:37:56
|
Duh. Still off the caffeine. Still suffering. NULLS still an issue though |
|
|
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. |
|
|
|