StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I read this differently from Cowan, and agree with Noah...</p> <p>Find all fruit where: - User 10 did not rate it - Atleast one other user did rate it</p> <p>However, in my experience using NOT IN can be quite slow. So, I generally prefer to filter using LEFT JOIN in the same way as Cowan. Here are a few different options, though I have not had time to test performance on large data sets...</p> <pre><code>SELECT [f].id, [f].name FROM fruit AS [f] INNER JOIN fruit_rating AS [fr] ON [fr].fruit_id = [f].id GROUP BY [f].id, [f].name HAVING SUM(CASE WHEN [fr_exclude].user_id = 10 THEN 1 ELSE 0 END) = 0 </code></pre> <p><br></p> <pre><code>SELECT [f].id, [f].name FROM fruit AS [f] INNER JOIN fruit_rating AS [fr] ON [fr].fruit_id = [f].id LEFT JOIN fruit_rating AS [fr_exclude] ON [fr_exclude].fruit_id = [fr].fruit_id AND [fr_exclude].user_id = 10 GROUP BY [f].id, [f].name HAVING MAX([fr_exclude].user_id) IS NULL </code></pre> <p><br> As this only works for one user, I would also consider making a table of "users to exclude" and LEFT JOIN on that instead... </p> <pre><code>SELECT [f].id, [f].name FROM fruit AS [f] INNER JOIN fruit_rating AS [fr] ON [fr].fruit_id = [f].id LEFT JOIN excluded_users AS [ex] AND [ex].user_id = [fr].user_id GROUP BY [f].id, [f].name HAVING MAX([ex].user_id) IS NULL </code></pre> <p><br></p> <p>Or something much more long winded, but I suspect is the fastest on larger data sets with appropriate indexes...</p> <pre><code>SELECT [f].id, [f].name FROM fruit [f] INNER JOIN ( SELECT fruit_id FROM fruit_rating GROUP BY fruit_id ) AS [rated] ON [rated].fruit_id = [f].id LEFT JOIN ( SELECT [fr].fruit_id FROM fruit_rating AS [fr] INNER JOIN excluded_users AS [ex] ON [ex].user_id = [fr].user_id GROUP BY [fr].fruit_id ) AS [excluded] ON [rated].fruit_id = [excluded].fruit_id WHERE [excluded].fruit_id IS NULL GROUP BY [f].id, [f].name </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