sql server query/subquery question

Question

Hi Experts, I have a tables with data like this

Id  BookId  TagId
34  113421  9
35  113421  10
36  113421  11
37  113421  1
38  113422  9
39  113422  1
40  113422  12

I need to write a query (SQL Server) which gives me data according to the tags say if I want bookIds where tagid =9 it should return bookid 113421 and 113422 as it exists in both the books, but If I ask data for tags 9 and 10 it should return only book 113421 as that is the only book where both the tags are present.

Thanks

Parminder

Answer 1

SELECT * FROM Books WHERE TagId IN (9,10)

Answer 2

The following should work:

SELECT
  BookId
FROM
  BookTags
WHERE
  TagId IN (9,10)
GROUP BY BookId HAVING COUNT(*) = 2

You need to set the HAVING COUNT(*) = x bit such that x is equal to the number of tags you're searching for (so in this case, it's 2. If you wanted 9, 10 & 11, you'd set it to 3, etc)

(Note: this is assuming you don't have books with duplicate duplicate TagId values)

Answer 3

Select distinct BookId From Books Where TagId = 9

intersect

Select distinct BookId From Books Where TagId = 10

Hope this helps