StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First of all what bažmegakapa said is correct, and more. If I understood correctly the setup you described, you are already wasting considerable space (and performances).</p> <p>You can do this:</p> <pre><code>CREATE TABLE pleft ( id integer, name varchar(20), tags integer ); CREATE TABLE pright ( id integer, tag varchar(20)); INSERT INTO pleft VALUES ( 1, 'Bob', 1 ), ( 9, 'Bob', 4 ), ( 15, 'Bob', 6 ); INSERT INTO pleft VALUES ( 2, 'Ann', 1 ), ( 3, 'Joe', 4 ), ( 4, 'Joe', 6 ); INSERT INTO pright VALUES ( 1, 'Sailing' ), ( 4, 'Snowboarding' ), ( 6, 'Skiing' ); SELECT pleft.name, GROUP_CONCAT(pright.tag) FROM pleft JOIN pright ON ( pleft.tags = pright.id ) GROUP BY pleft.name ORDER BY pleft.name; +------+-----------------------------+ | name | GROUP_CONCAT(pright.tag) | +------+-----------------------------+ | Ann | Sailing | | Bob | Sailing,Skiing,Snowboarding | | Joe | Snowboarding,Skiing | +------+-----------------------------+ </code></pre> <p>...but notice how the names are needlessly duplicated in each row of the <code>pleft</code> table. Ideally you would have one table modeling the person: (id=1, name="Bob"), one table modeling the tags (id=6, value="Skiing") and one table containing their relation. This would ensure that, e.g., Bob decides to go by "Robert", you don't have to de-bob the whole tags table, but only the one row involving Bob.</p> <p><strong>UPDATE</strong></p> <p>So <code>tags</code> is a varchar field holding "1,4,6". Same logic applies, but now we have to split the field before reuniting it again. You cannot use something like "1 in tags" since "11" would return true ("1" is contained in "11" after all). (This is the SQL Antipattern known as "Jaywalking": see e.g. <a href="https://groups.google.com/forum/?fromgroups=#!topic/django-users/5j4AmQE6nTk" rel="nofollow">https://groups.google.com/forum/?fromgroups=#!topic/django-users/5j4AmQE6nTk</a> )</p> <pre><code>SELECT pleft.name, GROUP_CONCAT(pright.tag) FROM pleft JOIN pright ON ( CONCAT(',',pleft.tags,',') LIKE CONCAT('%,',pright.id,',%' )) GROUP BY pleft.name ORDER BY pleft.name; </code></pre> <p>Another way would be to have a stored procedure: see <a href="http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/" rel="nofollow">http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/</a> .</p> <pre><code>CREATE TABLE pleft ( id integer, name varchar(20), tags varchar(20) ); INSERT INTO pleft VALUES ( 1, 'Bob', '1,4,6' ), ( 2, 'Jill', '4,1' ); SELECT pleft.name, GROUP_CONCAT(pright.tag) FROM pleft JOIN pright ON ( CONCAT(',',pleft.tags,',') LIKE CONCAT('%,',pright.id,',%' )) GROUP BY pleft.name ORDER BY pleft.name; +------+-----------------------------+ | name | GROUP_CONCAT(pright.tag) | +------+-----------------------------+ | Bob | Sailing,Snowboarding,Skiing | | Jill | Sailing,Snowboarding | +------+-----------------------------+ </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