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 2000 Forums  Transact-SQL (2000)  join on NORTHWIND issue

Author  Topic  Kali Starting Member 14 Posts Posted - 2004-08-18 : 22:39:00 Hi all, I have an sql brainfart, can’t get the join straight. I am trying to join 4 systables to get the names of tables, non-clustered indexes on them and columns on which these indexes reside. Here’s the join on Northwind db: select so.name as [Table Name], SC.NAME AS [COLUMN], si.name as [Index Name], SC.NAME AS [COLUMN] from sysobjects so join syscolumns sc on so.id = sc.id join sysindexes si on sc.id = si.id join sysindexkeys sik on si.id = sik.id and sc.colid = sik.colid join sysfilegroups sf on sf.groupid = si.groupid where si.indid >1 and sf.groupname = 'primary' and so.xtype = 'U' and si.name not like '_WA_Sys%' problem is, off course, that on Orders table, for example, there are only total of 9 non-clustered indexes, but I get 72 rows just for that table. It’s the join sequence I bet, but I can’t get it right. Help much appreciated