Stuck in writing query... - 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 2000 Forums

SQL Server Development (2000)

Stuck in writing query...

Author

Topic

juwerian
Starting Member

2 Posts

Posted - 2006-10-29 : 10:06:09

Hello all,

I have a table names Keyword having two columns named BookID(for storing Books IDs) and KWord(keyword).
I used the following query to fetch the those particular BookIDs which have specified keywords.

SELECT BookID
FROM Keyword
WHERE (KWord IN ('SQL', 'ERD', ' xxx'))

The problem here is that i am unable to write a query which can return exclusive resultset (means if xxx is not present in the table then it should return empty resultset)

Any idea???????

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-29 : 10:57:07

I am not sure of the requirements

SELECT		BookID
FROM		Keyword
WHERE		KWord IN ('SQL', 'ERD', 'xxx')
GROUP BY	BookID
HAVING		SUM(case when kword = 'xxx' then 1 else 0 end) > 0

Peter Larsson
Helsingborg, Sweden

juwerian
Starting Member

2 Posts

Posted - 2006-10-30 : 14:12:00

Following is the sample table:

Keyword

BookID Kword
B1--------SQL
B1--------ERD
B2--------xxx
B3--------ERD

I want to query for BookID(s) having kword 'SQL' AND 'ERD' (both at a time, not just one of them). As a result, B1 should be my expected result but i am unable to write query for this.

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 14:16:41

[code]select bookid
from mytable
group by bookid
having sum(case when kword = 'sql' then 1 else 0 end) > 0
and sum(case when kword = 'erd' then 1 else 0 end) > 0[/code]

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-30 : 20:13:06

or

select bookid
from mytable
where kword in ('sql','erd')
group by bookid
having count(*)=2

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 00:32:45

Even this?

BookID Kword
B1--------SQL
B1--------ERD
B1--------ERD

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources