Query help needed - 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)

Query help needed

Author

Topic

ritu2303
Starting Member

4 Posts

Posted - 2009-04-16 : 06:37:27

I have a table.

TableID KeywordId RecordID keywordXrefID
1 20 8 1
1 20 13 2
1 20 15 3
1 30 8 4
1 30 14 5
1 30 15 6
1 40 8 7

If i pass the parameter keywordid(20,30,40) it should return me recordid 8 and if i pass the parameter keywordid(20,30) it should return the record id 8,15.

Please help me in doing this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 06:57:11

select RecordID from your_table
group by RecordID
having sum(case when KeywordId in (20,30,40) then 1 else 0 end)=3

Madhivanan

Failing to plan is Planning to fail

ritu2303
Starting Member

4 Posts

Posted - 2009-04-16 : 07:03:41

Hi madhivanan

Thanks for replying for not working out.It is returning me no record id.

One more thing why we are using sum clause

select RecordID from your_table
group by RecordID
having sum(case when KeywordId in (20,30,40) then 1 else 0 end)=3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 07:07:04

quote:
Originally posted by ritu2303

Hi madhivanan

Thanks for replying for not working out.It is returning me no record id.

One more thing why we are using sum clause

select RecordID from your_table
group by RecordID
having sum(case when KeywordId in (20,30,40) then 1 else 0 end)=3

Based on the sample data

declare @t table(TableID int , KeywordId int, RecordID int, keywordXrefID int)
insert into @t
select 1, 20, 8, 1 union all
select 1, 20, 13, 2 union all
select 1, 20, 15, 3 union all
select 1, 30, 8, 4 union all
select 1, 30, 14, 5 union all
select 1, 30, 15, 6 union all
select 1, 40, 8, 7

select RecordID from @t
group by RecordID
having sum(case when KeywordId in (20,30,40) then 1 else 0 end)=3

Madhivanan

Failing to plan is Planning to fail

ritu2303
Starting Member

4 Posts

Posted - 2009-04-16 : 07:15:29

With ur data it is returning me the recordid 8 but if we remove the 40 from keyowrd(20,20,40) it is not returning anything

ritu2303
Starting Member

4 Posts

Posted - 2009-04-16 : 07:38:26

Its solved. So please dont try.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-16 : 07:45:14

Care to share your solution?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources