StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMYSQL join results set wiped results during IN () in where clause?
text
Body
copied!<p><strong>Editted heavily!</strong> </p> <p>The original question was based on a misunderstanding of how IN() treats a column from a results set from a join. I thought IN( some_join.some_column ) would treat a results column as a list and loop through each row in place. It turns out it only looks at the first row. </p> <p>So, the adapted question: Is there anything in MySQL that can loop through a column of results from a join from a WHERE clause?</p> <p>Here's the super-simplified code I'm working with, stripped down from a complex crm search function. The left join and general idea are relics from that query. So for this query, it has to be an exclusive search - finding people with ALL specified tags, not just any.</p> <p>First the DB</p> <p>Table 1: Person</p> <pre><code>+----+------+ | id | name | +----+------+ | 1 | Bob | | 2 | Jill | +----+------+ </code></pre> <p>Table 2: Tag</p> <pre><code>+-----------+--------+ | person_id | tag_id | +-----------+--------+ | 1 | 1 | | 1 | 2 | | 2 | 2 | | 2 | 3 | +-----------+--------+ </code></pre> <p>Nice and simple. So, naturally:</p> <pre><code>SELECT name, GROUP_CONCAT(tag.tag_id) FROM person LEFT JOIN tag ON person.id = tag.person_id GROUP BY name; +------+--------------------------+ | name | GROUP_CONCAT(tag.tag_id) | +------+--------------------------+ | Bob | 1,2 | | Jill | 2,3 | +------+--------------------------+ </code></pre> <p>So far so good. So what I'm looking for is something that would find only Bob in the first case and only Jill in the second - without using HAVING COUNT(DISTINCT ...) because that doesn't work in the broader query (there's a seperate tags inheritance cache and a ton of other stuff).</p> <p>Here's my original sample queries - based on the false idea that IN() would loop through all rows at once.</p> <pre><code>SELECT DISTINCT name FROM person LEFT JOIN tag ON person.id = tag.person_id WHERE ( ( 1 IN (tag.tag_id) ) AND ( 2 IN (tag.tag_id) ) ); Empty set (0.00 sec) SELECT DISTINCT name FROM person LEFT JOIN tag ON person.id = tag.person_id WHERE ( ( 2 IN (tag.tag_id) ) AND ( 3 IN (tag.tag_id) ) ); Empty set (0.00 sec) </code></pre> <p>Here's my new latest failed attempt to give an idea of what I'm aiming for...</p> <pre><code>SELECT name, GROUP_CONCAT(tag.tag_id) FROM person LEFT JOIN tag ON person.id = tag.person_id GROUP BY person.id HAVING ( ( 1 IN (GROUP_CONCAT(tag.tag_id) ) ) ) AND ( 2 IN (GROUP_CONCAT(tag.tag_id)) ); Empty set (0.00 sec) </code></pre> <p>So it seems it's taking a GROUP_CONCAT string, of either 1,2 or 2,3, and is treating it as a single entity rather than an expression list. Is there any way to turn a grouped column into an expression list that IN () or =ANY() will treat as a list?</p> <p>Essentially, I'm trying to make IN() loop iteratively over something that resembles an array or a dynamic expression list, which contains all the rows of data that come from a join. </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