need help finding records - 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)

need help finding records

Author

Topic

SQL_Rookie
Starting Member

32 Posts

Posted - 2009-05-01 : 09:20:19

this "should" be very simple having brain block right now....
I have a table that has this structure
ID R_ID F_ID S_ID
645 91 121 90
646 92 47 188
647 93 47 188
648 93 47 189
649 93 48 189
650 94 91 211

I want to FIND all the F_ID that have different R_ID associated with it? Like 47 above it has R_ID of 93 and 92. If it only has one R_ID associated with it then is ok.

I have tried

select F_ID, R_ID
from lkp
group by F_ID, R_ID
order by F_ID, R_ID

this gives me back all the ones that has the same R_ID too which I don't care for.

Any help would be appreciated...

SQL_Rookie
Starting Member

32 Posts

Posted - 2009-05-01 : 09:51:45

I got it finally WOKE up had some coffee....haha

Select t_lkp.F_ID, count(*)
FROM (select F_ID, R_ID
from lkp
group by F_ID, R_ID) as t_lkp
group by t_lkp.F_ID
having count(*) > 1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-01 : 09:55:32

this?

declare @t table (id int,r_id int,f_id int)
insert @t
select 645, 91, 121 union all
select 646, 92, 47 union all
select 647, 93, 47 union all
select 648, 93, 47 union all
select 649, 93, 48 union all
select 650, 94, 91

select t.f_id from
(
select row_number() over(partition by f_id order by r_id) as seq, r_id,f_id
from @t group by r_id,f_id
) t
where t.seq = 2

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-01 : 09:56:10

oops..had my window open too long...glad you figured it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources