StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POselect group_concat vs group_concat on left join
text
Body
copied!<p>I'm analyzing a few queries and have noticed some interesting results when trying to extract multiple fields from different tables based on a <code>user_id</code>. Consider the following tables:</p> <pre><code>+------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------------+------------------+------+-----+---------+----------------+ | subscription_id | int(11) unsigned | NO | PRI | NULL | auto_increment | | user_id | int(11) unsigned | YES | MUL | NULL | | | expires | int(11) unsigned | YES | MUL | NULL | | | created | int(11) unsigned | YES | MUL | NULL | | | modified | int(11) | YES | | NULL | | +------------------------+------------------+------+-----+---------+----------------+ </code></pre> <p>I want to <code>GROUP_CONCAT</code> all of a user's subscriptions when fetching users. Currently this method works for one field:</p> <pre><code>SELECT u.id, ( SELECT GROUP_CONCAT(s.subscription_id) FROM subscriptions s WHERE s.user_id = u.id ) AS subscription_ids FROM users u LIMIT 10 </code></pre> <p>And I can add all of the fields with different <code>SELECT GROUP_CONCAT</code>s. However this method is <strong>very slow</strong> with any real number of rows due to MySQL joining the <code>subscriptions</code> table for every field. Is there a way that I can <code>GROUP_CONCAT</code> all of the fields at once when listing users?</p> <p>I've tried a <code>LEFT JOIN</code> method:</p> <pre><code>SELECT u.id AS user_id, GROUP_CONCAT(s.subscription_id) AS subscription_ids FROM users u LEFT JOIN subscriptions s ON s.user_id = u.id LIMIT 10 </code></pre> <p>However, that appears to concatenate <strong>all</strong> of the subscription_ids. I'm a bit stumped why the <code>LEFT JOIN</code> would do that. But I'm surprised there doesn't appear to be an easy way to concatenate a list of fields from a different table.</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