query or script to check link table

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)

query or script to check link table

Author

Topic

warnockm
Starting Member

3 Posts

Posted - 2008-03-23 : 23:28:50

i'm pretty familiar w/ mysql, but relatively new to ms sql. Since i can do a lot more w/ ms sql, I have been trying to do all tasks in sql queries or scripts, but i'm stumped on this one. I have a table that links two things together. t1 has rows id1 and id2. When a row has these two id's, there is a "link" between them. I'm trying to make a command or script that will see if there is only 2 links for any given id.

for example, it would loop through each row, and run this command:

SELECT COUNT(*) AS Expr1
FROM links
WHERE (Id1 = this_rows_id1) OR (Id2 = this_rows_id1)

and again w/ id2

and then i would be interested in any row that only had a count of 2. is this possible?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-24 : 01:44:09

Please post some sample data & the desired output.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-24 : 02:10:04

If I am not mistaken then this is what you want.Since you have no experience in SQL 2005 it may be a little complicated for you to understand the query.

Declare @tbl table(id int,t1 varchar(10))
insert into @tbl
select 1,'id1' union all
select 1,'id2'union all
select 2,'id2'

--To check the data

select Id,[Count] from
(select count(id)as
'Count',id, row_number()over(partition by id order by count(*) desc)as rowid
from @tbl group by id)t

--To check whether there are 2 links for a given id

select Id,[Count] from
(select count(id)as
'Count',id, row_number()over(partition by id order by count(*) desc)as rowid
from @tbl group by id)t where [Count]>=2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources