Have Geek Brain

Sql server query / subquery

I have 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) that gives me data according to tags, if I want books where tagid = 9, it should return bookid 113421 and 113422 as it exists in both books, but if I ask for data for tags 9 and 10, it should only return book 113421, as this is the only book that has both tags present.

thanks

Parminder

+2


a source to share


3 answers


The following should work:

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


You need to set the bit HAVING COUNT(*) = x

so that it x

is equal to the number of tags you are looking for (so in this case it is 2. If you want 9, 10 and 11, you must set it to 3, etc.)

(Note: It is assumed that you do not have any books with duplicate duplicate TagId values)

+2


a source


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



0


a source 


Select distinct BookId From Books Where TagId = 9

intersect

Select distinct BookId From Books Where TagId = 10

Hope it helps

0


a source






More articles:


All Articles