How to do this mysql query? (how to get n fields in 1 row)

Question

Following a previous question ( http://stackoverflow.com/questions/652484/how-to-do-this-query-in-mysql )

Lets say I have a message:

Id: 1, Message: This is a message

2 subjects:

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

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
Id: 3, message_id: 1, subject_id: 3

I wanted to do a query to select the messages that had Math AND Science as subjects I ended up using:

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');

Now, its very clear that i would like to show the message(because it does have those 2 subjects) and also tell the user that it doesn't ONLY have those 2 subject, but it actually have 3, and not only that... i would like to print the 3 subjects(Of course i have lots of messages and would like to actually list them, with their respective subjects in just 1 query). I cant seem to understand if that is actually possible with just one query, since i would get n "subject" field as a return.

Any idea if this can be done with just one query and if so, how?

TY

Answer 1

Have you tried GROUP_CONCAT?

Answer 2

See GROUP_CONCAT for one solution. I don't know how efficient this approach is, however.

select message.Id as id, Message, group_concat(Subject separator ';') as subjects
from message,subject,message_subject_rel
where message.Id=message_subject_rel.message_id
  and subject.Id=message_subject_rel.subject_id
group by message.Id
having subjects like '%Math%' and subjects like '%Science%';

From that, I get:

+------+---------+----------------------+
| id   | Message | subjects             |
+------+---------+----------------------+
|    1 | Message | Math;Science;Numbers | 
+------+---------+----------------------+

Obviously, you could do a better job than my ';' separator and my like clauses.