StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Ok, hope I understood it right, what you want. I would first select every message that is from or to the current user (ID=205) like this:</p> <pre><code>SELECT * FROM mensajes WHERE (from_memberID=205 OR to_memberID=205) </code></pre> <p>Now we want to group conversations together, which means by the ID of the user thats not having ID=205. We can achieve this by using an IF statement. IF works this way: <strong>IF(condition,value-if-true,value-if-false)</strong>. So we create a virtual column <strong>otherID</strong>, which contains the conversation partner userid: <code>IF(from_memberID=205,to_memberID,from_memberID) AS otherID</code>. Then the SQL looks like this:</p> <pre><code>SELECT ID, IF(from_memberID=205,to_memberID,from_memberID) AS otherID, message, Date FROM mensajes WHERE (from_memberID=205 OR to_memberID=205) </code></pre> <p>Now we just need to add a <code>GROUP BY otherID</code> and an <code>ORDER BY Date</code> (last posting is kept in a group):</p> <pre><code>SELECT ID, IF(from_memberID=205,to_memberID,from_memberID) AS otherID, message, Date FROM mensajes WHERE (from_memberID=205 OR to_memberID=205) GROUP BY otherID ORDER BY Date </code></pre> <p>Now we can join this together with the table <code>miembros</code> using the SQL above as a subselect:</p> <pre><code>SELECT * FROM (SELECT ID, IF(from_memberID=205,to_memberID,from_memberID) AS otherID, message, Date FROM mensajes WHERE (from_memberID=205 OR to_memberID=205) GROUP BY otherID ORDER BY Date) AS last INNER JOIN miembros as mem ON mem.ID=lastotherID; </code></pre>

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