StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can accomplish this by building a set of users that matches the criterias from your profile tables, something like this:</p> <pre><code>SELECT FK_USERS_ID FROM Profiles WHERE topic='x' AND TOPIC_ID IN (1,2) GROUP BY FK_USERS_ID HAVING COUNT(1) = 2 </code></pre> <p>Here you list your users that matches the topics you need. By grouping by the user id and specifying the amount of rows that should be returned, you can effectively say "only those that has x and y in topic z. Just make sure that the COUNT(1) = x has the same number of different TOPIC_IDs to look for.</p> <p>You can then query the user table </p> <pre><code>SELECT ID FROM Users WHERE name like '%PAU%' AND ID IN (<insert above query here>) </code></pre> <p>You can also do it in a join and a derived table, but the essence should be explained above.</p> <p>EDIT: if you are looking for multiple combinations, you can use mysql's multi-column IN:</p> <pre><code>SELECT FK_USERS_ID FROM Profiles WHERE (topic,topic_id) IN (('x',3),('x',5),('y',3),('y',6)) GROUP BY FK_USERS_ID HAVING COUNT(1) = 4 </code></pre> <p>This will look for uses matching the pairs x-3, x-5, y-3 and y-6.</p> <p>You should be able to build the topic-topic_id pairs easily in php and stuffing it into the SQL string, and also just counting the number of pairs you generate into a variable for using for the count(1) number. See <a href="http://www.mysqlperformanceblog.com/2008/04/04/multi-column-in-clause-unexpected-mysql-issue/" rel="nofollow">http://www.mysqlperformanceblog.com/2008/04/04/multi-column-in-clause-unexpected-mysql-issue/</a> for performance talk using this approach.</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