How to do this query in Mysql?

Question

I have 3 tables, message, subject and message_subject_rel. The idea is to have messages that can relate to a lot of subjects and then do a cross subject search.

Lets say I have a message:

Id: 1, Message: This is a message

2 subjects:

Id:1, Subject: Math
Id:2, Subject: Science

And there's 2 message_subject_rel entries that go:

Id: 1, message_id: 1, subject_id: 1
Id: 2, message_id: 1, subject_id: 2

If i wanted to search the messages that are related with math, I would do a simple join with the 3 tables and the where clause would be subject = "Math"

But what I don't know how to do, is search for the messages that are related with math AND Science. If i do a simple join i get tables with something like:

id  message  user_id  created_at  ip  id  message_id  subject_id  id  subject

And if I do a where subject = "Math" and subject = "Science" i wont get any results because each message will only have 1 subject related in each row, but duplicated rows for messages with more than 1 subject.

So, what do you recommend?

Answer 1

Do an inner select in your where clause.

SELECT FROM [tables and joins] WHERE subject = "Math" AND message_id IN (SELECT message_id FROM [tables and joins] WHERE subject = "Science")

Answer 2

This is basically the same question as http://stackoverflow.com/questions/648308/filtering-from-join-table/

I'll adapt my answer from that question.

JOIN solution:

SELECT m.*
FROM messages m
 JOIN message_subject_rel ms1 ON (m.id = ms1.message_id)
 JOIN subjects s1 ON (ms1.subject_id = s1.id AND s1.subject = 'Math')
 JOIN message_subject_rel ms2 ON (m.id = ms1.message_id)
 JOIN subjects s2 ON (ms2.subject_id = s2.id AND s2.subject = 'Science');

GROUP BY solution:

Note that you need to list all m.* columns in the GROUP BY clause, unless you use MySQL.

SELECT m.*
FROM messages m 
 JOIN message_subject_rel ms ON (m.id = ms.message_id)
 JOIN subjects s ON (ms.subject_id = s.id)
WHERE s.subject IN ('Math', 'Science'))
GROUP BY m.id, ...
HAVING COUNT(*) = 2;

Subquery solution:

SELECT m.*
FROM messages m
WHERE m.id = ANY (SELECT message_id 
                  FROM message_subject_rel ms JOIN subjects s 
                    ON (ms.subject_id = s.id) 
                  WHERE s.subject = 'Math')
  AND m.id = ANY (SELECT message_id 
                  FROM message_subject_rel ms JOIN subjects s 
                    ON (ms.subject_id = s.id) 
                  WHERE s.subject = 'Science');

Modified GROUP BY solution:

Simplifies GROUP BY clause by isolating search in a subquery.

SELECT m.*
FROM messages m
WHERE m.id IN (
  SELECT ms.message_id FROM message_subject_rel ms JOIN subjects s
    ON (ms.subject_id = s.id)
  WHERE s.subject IN ('Math', 'Science'))
  GROUP BY ms.message_id HAVING COUNT(*) = 2
);

PS: There's no reason your message_subject_rel table needs an ID column.