StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If I understand correctly, you have a many-to-many relationship where you want to satisfy multiple conditions at once on the same field.</p> <p>In order to do this, you need to use MySQL's <code>HAVING</code> clause. Since I don't know your database structure, I'll make one for this example.</p> <p><strong>object</strong></p> <pre><code>+------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | object_id | int(11) | NO | PRI | NULL | auto_increment | | title | varchar(255) | NO | | NULL | | | otherfield | text | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ </code></pre> <p><strong>tag</strong></p> <pre><code>+--------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+----------------+ | tag_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | +--------+--------------+------+-----+---------+----------------+ </code></pre> <p><strong>object_tag</strong></p> <pre><code>+-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | object_id | int(11) | NO | | NULL | | | tag_id | int(11) | NO | | NULL | | +-----------+---------+------+-----+---------+-------+ </code></pre> <p>Now, to retrieve an object with all its tags, you would do:</p> <pre><code>SELECT object.*,tag.name as tag FROM object LEFT JOIN object_tag ON object_tag.object_id = object.object_id LEFT JOIN tag ON object_tag.tag_id = tag.tag_id </code></pre> <p>That gives you something like:</p> <pre><code>+-----------+-----------+------------+------+ | object_id | title | otherfield | tag | +-----------+-----------+------------+------+ | 1 | My object | something | foo | | 1 | My object | something | bar | | 1 | My object | something | baz | +-----------+-----------+------------+------+ </code></pre> <p>Now, since you cannot do a <code>WHERE tag='foo' AND tag='bar'</code>, because it's impossible to match, you have to use the <code>HAVING</code> clause.</p> <pre><code>SELECT object.* FROM object LEFT JOIN object_tag ON object_tag.object_id = object.object_id LEFT JOIN tag ON object_tag.tag_id = tag.tag_id WHERE tag.name IN ('foo', 'bar') GROUP BY object.object_id HAVING COUNT(1) >= 2; </code></pre> <p>Now this will give you:</p> <pre><code>+-----------+-----------+------------+ | object_id | title | otherfield | +-----------+-----------+------------+ | 1 | My object | something | +-----------+-----------+------------+ </code></pre> <p>The <code>HAVING</code> clause tells MySQL to return only if 2 tags or more (<code>COUNT(1) >= 2</code>) matches the result set.</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