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:

*"I tried IN clause but it is not working as per the expectation."* Then show us your attempt. Show us your sample data (not as an image) and your expect results. — Thom A, Nov 29 '19 at 09:48
If you used `select * from ThatTable where CategoryID in (1,2)` youd' get the first 5 lines. If you got `CategoryID = 3` it would *not* be fine at all — Panagiotis Kanavos, Nov 29 '19 at 09:50
What about the sample data and expected results, @JeetenParmar ? — Thom A, Nov 29 '19 at 09:51
@JeetenParmar *"Show us your sample data (**not as an image**)*". YOU have enough reputation to know why images of code/data aren't helpful to those you're asking for help from. — Thom A, Nov 29 '19 at 09:54
@JeetenParmar ` It should return BlogID 3` no it shouldn't. There are 5 rows with CategoryID equal to 1 or 2. If you *don't* want specific BlogIDs, you should add the restriction in the query itself. You should also explain the logic that returns only BlogID 3 instead of the others — Panagiotis Kanavos, Nov 29 '19 at 09:54
@JeetenParmar, Check my answer. You want **1 and 3** or you want **3** only? — DineshDB, Nov 29 '19 at 09:55
@DineshDB If I provide BlogID=1 then It should return data as per the expected Output. — Jeeten Parmar, Nov 29 '19 at 09:57
@DineshDB your answer is based on an arbitrary restriction. The OP didn't ask for blogs with only 3 categories, or blogs with only 1 category — Panagiotis Kanavos, Nov 29 '19 at 09:57
@JeetenParmar based on *what* logic? Why only BlogID 3? We can't guess and neither can the server. Why *not* BlogID 1 and 2? — Panagiotis Kanavos, Nov 29 '19 at 09:57
@PanagiotisKanavos I am looking for those Blogs whose Categories are same or more based on the input BlogID. — Jeeten Parmar, Nov 29 '19 at 09:58
@JeetenParmar Is the real question `Blogs that match *all* categories of another blog*? Update the question with actual text, not images, and explain what you actually want. Don't force people to guess what's on your mind — Panagiotis Kanavos, Nov 29 '19 at 09:59
@JeetenParmar you repeated the same ambiguous phrase several times already. It's just as ambiguous as it was when you first wrote it. That's why the question is heavily downvoted and may well get closed soon — Panagiotis Kanavos, Nov 29 '19 at 09:59
So the logic that you are looking for is; `1) get categoryID for BlogID=1. -> based on your data you get 1 and 2. 2) Now look for all the other BlogID's which have both of these categories i.e 1 & 2. 3) Return BLOGID's and CATEGORYID's for all those blogs.` Is this the correct guess? — ankyskywalker, Nov 29 '19 at 10:10
@ankyskywalker yes, logic is correct and return only blog ids. — Jeeten Parmar, Nov 29 '19 at 10:14

DineshDB · Accepted Answer · 2019-11-29T10:16:23.913

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

score 0 · Answer 2 · edited Nov 29 '19 at 09:55

0

If I got you correctly, You could use

select * from tblBlogCategory where BlogID=1

you should get all CategoryID where BlogID=1 which you may also further filter

 select * from tblBlogCategory where BlogID=1 and CategoryID in (1,2)

edited Nov 29 '19 at 09:55

Amira Bedhiafi

8,088
6
24
60

answered Nov 29 '19 at 09:53

Gnyasha

658
11
19

score 0 · Answer 3 · answered Nov 29 '19 at 13:28

If you just want to return blog ids that have matches, then this should be sufficient:

select bc.blogid
from tblBlogCategory bc join
     tblBlogCategory bc1
     on bc1.categoryid = bc.categoryid and
        bc1.blogid = 1 and
        bc1.blogid <> bc.blogid
group by bc.blogid
having count(*) = (select count(*) from tblBlogCategory bc where bc.blogid = 1);

Find similar records in the same table

3 Answers3