CODEWITHSUNDEEP
sqlsql-servert-sql
Nevo David
Nevo David

Reputation: 137

Sql Server - Matching exact ids or more

I have a "messages" table with columns: "id", "title",

"categories" table with columns: "id", "title",

and "messages_categories" link table with columns: "message_id","category_id".

lets assume we have messages with ids of 

1,2,3

and categories with ids of

1,2,3

messages_categories with data of

message: 1, category: 1

message: 2, category: 1

message: 2, category: 2

message: 3, category: 1

message: 3, category: 2

message: 3, category: 3

I want to find the exact match or more for example

if I search for category 1 i'll get messages 1,2,3

if I search for category 1,2 i'll get messages 2,3

if I search for category 1,2,3 i'll get only message 3

i'm using a lot of ids so join for every category can be too much.

I figured out I can use "having" statement with "sum" and "count" to find the exact rows but it's not good enough.

Appreciate any help, Nevo.

Upvotes: 2

Views: 287

Answers (2)

Serg
Serg

Reputation: 22811

If you need "more" then use NOT EXISTS, so called relational division with reminder.

SELECT DISTINCT messages_id
FROM messages_categories r1
WHERE NOT EXISTS
       (SELECT *
        FROM 
           (SELECT 1 as cat_id UNION SELECT 2 ) S -- id of categories needed 
        WHERE NOT EXISTS
               (SELECT *
                FROM messages_categories AS r2
                WHERE (r1.messages_id = r2.messages_id)
                   AND (r2.category_id = S.cat_id)));

Upvotes: 1

Kevin Anderson
Kevin Anderson

Reputation: 4592

Something along the lines of this might work:

SELECT DISTINCT x.message_id
FROM (
   **query-that-gives-the-exact-rows-but-isnt-good-enough**
) AS x

Upvotes: 0

Related Questions