creating view help? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

creating view help?

Author

Topic

ShooterJ07
Starting Member

17 Posts

Posted - 2007-08-13 : 12:43:10

I made a post here a few days ago & got a response that was a huge help. Thanks to everyone who posts.. the forums are a big help.

I have a table, call it Users. Looks like this:

UserID, Username
1, John
2, Joe
3, Dan

I need to create a view from this table that looks like this:

Index1, Index2
John, Joe
John, Dan
Joe, John
Joe, Dan
Dan, John
Dan, Joe

Basically.. I need every single person to be listed as Index1 along with every other person as Index2. N *(N-1) records in the view for every N users in Table1.

Anybody have a suggestion? I'm trying everything & can't get it right.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-13 : 12:51:31

try a CROSS JOIN


select distinct T1.Username , T2.Username
from YourTable T1
cross join YourTable T2 
where T1.Username <>  T2.Username

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ShooterJ07
Starting Member

17 Posts

Posted - 2007-08-13 : 12:57:25

Perfect dinakar. You're 2 for 2. :)

I gotta remember the crossjoin. I was using every other kind of join imaginable.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 17:19:59

select distinct T1.Username , T2.Username
from YourTable T1
inner join YourTable T2 on T1.Username <> T2.Username

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-28 : 02:50:50

Both left and right joins also seem to work in this case

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources