StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It's not exactly clear what you mean when you say "I want all users that are in group 1, 3, and 8" and then write</p> <pre><code>select distinct(user_id) from group_user_map where group_id in (58,226) </code></pre> <p>because the English suggests you want a user who is in all three groups but the SQL gives you users who are in any 1 of the groups. So you need to be clearer about what exactly you want.</p> <p>It's a little hard to believe that you are trying to find users that are in all of 4 supergroups with each supergroup being made of exactly 2 groups. It makes me question what you are doing and why. </p> <p>There are a few different approaches I can think of depending on what you are really going to run into. Obviously the simplest is to break it into multiple queries and combine the results in your code. You can auto-join the group table if it's not too big, but it probably is too big to to join 3 times. You might get better performance with <code>NOT EXISTS</code> than with <code>NOT IN</code> but probably not. You can try to further leverage aggregation functions with <code>CASE</code> functions to compute success values in an intermediate table, but that's getting pretty crazy. More likely you'd be better off reworking your data structure. </p> <p>The main problem I see with your existing solution is the large number of temporary tables you create. In general you are going to need a temporary table of some kind to do something this complicated so I would focus on limiting it to two tables, each of which is smaller than the relationships 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