StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to do this mysql query? (how to get n fields in 1 row)
text
Body
copied!<p>Following a previous question ( <a href="https://stackoverflow.com/questions/652484/how-to-do-this-query-in-mysql">How to do this query in Mysql?</a> ) </p> <p>Lets say I have a message:</p> <pre><code>Id: 1, Message: This is a message </code></pre> <p>2 subjects:</p> <pre><code>Id:1, Subject: Math Id:2, Subject: Science Id:3, Subject: Numbers </code></pre> <p>And there's 2 message_subject_rel entries that go:</p> <pre><code>Id: 1, message_id: 1, subject_id: 1 Id: 2, message_id: 1, subject_id: 2 Id: 3, message_id: 1, subject_id: 3 </code></pre> <p>I wanted to do a query to select the messages that had Math AND Science as subjects I ended up using:</p> <pre><code>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'); </code></pre> <p>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. </p> <p>Any idea if this can be done with just one query and if so, how?</p> <p>TY</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload