Find similar records in the same table

Question

I am looking for similar records in the same table. I tried IN clause (below query) but it is not working as per the expectation.

Select * from tblBlogCategory Where CategoryID IN (Select CategoryID from tblBlogCategory Where BlogID=1)

i.e. I have BlogID=1. I want those records in the table whose CategoryID is 1 and 2 both. It is also fine if any new CategoryID 3 is there. So, for the below table, It should return BlogID 3.

How do I achieve this?

Expected Output:

Answer 1

Try this:

Table Schema:

CREATE TABLE YourTable(BlogId INT, CategoryId INT)

INSERT INTO YourTable VALUES(1,1)
INSERT INTO YourTable VALUES(1,2)
INSERT INTO YourTable VALUES(2,1)
INSERT INTO YourTable VALUES(3,1)
INSERT INTO YourTable VALUES(3,2)
INSERT INTO YourTable VALUES(3,3)

Query:

DECLARE @BlogID INT = 1

SELECT * 
FROM YourTable  
WHERE BlogID IN(
    SELECT A.BlogID 
    FROM YourTable A
    JOIN (
        SELECT CategoryId
        FROM YourTable
        WHERE BlogID = @BlogID
    )B ON A.CategoryId = B.CategoryId
    GROUP BY A.BlogID
    HAVING COUNT(DISTINCT A.CategoryId)>=(SELECT COUNT(DISTINCT CategoryId) FROM YourTable WHERE BlogID = @BlogID)
  )
AND BlogID != @BlogID

Output:

| BlogId | CategoryId |
|--------|------------|
|      3 |          1 |
|      3 |          2 |
|      3 |          3 |

SQL Fiddle Link