StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You need to use the <code>GROUP BY</code>:</p> <pre><code> SELECT messages_id , title , message , user_id , avatar_id , avatar , count(*) as commentCount FROM messages inner join avatar on messages.user_id = avatar.user_id left join comments on messages.messages_id = comments.messages_id GROUP BY messages_id </code></pre> <p>This should work if:</p> <ul> <li><code>messages_id</code> is unique in <code>messages</code></li> <li><code>user_id</code> is unique in <code>avatar</code></li> </ul> <p>Otherwise you need to specify the value you want to get.</p> <p>Edited:</p> <p>I wrote <code>inner join</code> for <code>avatar</code>table thinking in that all users have an avatar. If this is not true should be <code>left join</code> like in your query.</p> <p><strong>Second try</strong></p> <p>Maybe the error was that the <code>group by</code> should be <code>messages.messages_id</code> instead of <code>messages_id</code>. In fact in others RDMBS this is an error:</p> <blockquote> <p>ERROR: column reference "messages_id" is ambiguous</p> </blockquote> <p>I'm going to be more precise:</p> <pre><code> SELECT m.messages_id as id , min(m.title) as title , min(m.message) as message , min(m.user_id) as user_id , min(a.avatar_id) as avatar_id , min(a.avatar) as avatar , count(c.comments_id) as commentCount FROM messages as m left join avatar as a on m.user_id = a.user_id left join comments as c on m.messages_id = c.messages_id GROUP BY m.messages_id </code></pre> <p>All the <code>min</code>could be deleted in MySQL if you are sure there is only one value. In standard SQL you must choose the value you want, if there is only one you can type <code>min</code> or <code>max</code>.</p> <p>I change join with avatar to be <code>left join</code>. Probably not all users have an avatar.</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