How can I get n results of a join in MySQL

Question

Right now I am using a loop to go trough 100 IDs that I have. Each iteration of the loop does a query to the DB. In my opinion this is not efficient at all. I want to put this into one query. But it's not going my own way to say the least.

Here is the query I have been playing around with. It should give a good idea of what I am trying to do.

SELECT
    festival_backers.clown_id,
    festival_backers.user_id
FROM festival_backers
INNER JOIN users ON users.id = festival_backers.user_id
LEFT JOIN festival_backers AS lesser ON lesser.festival_id = 1632 AND lesser.clown_id = festival_backers.clown_id
WHERE festival_backers.festival_id = 1632
  AND lesser.clown_id IN (136541, 1000376982, 222329...)
  AND lesser.user_id NOT IN (136541, 1000376982, 222329...)
GROUP BY festival_backers.clown_id
HAVING COUNT(lesser.clown_id) < 3
ORDER BY (festival_backers.amount + (festival_backers.free_vote / 10)) DESC
LIMIT 300;

I am getting nothing with this query.

The result I am looking for.

clown_id    | user_id
-----------------------------
136541      | 21324
136541      | 57889
136541      | 89632
1000376982  | 56432
1000376982  | 57343
1000376982  | 23345
222329      | 45456
222329      | 77854
222329      | 67789

I am trying to get the users that have backed a clown. But only three of them not more than that. I was able to make query where I got more than three but that's not good (they are just too many).

The reason I use NOT IN (following example) is because user_id is also used for clowns because they are also users.

AND lesser.user_id NOT IN [...]

I tried following this: https://stackoverflow.com/a/30269273/736910

Is there a way to maybe use a function or something to limit the amount of results return with a join to three? Or is there some other way to get the result I am looking for?

UPDATE

I have different queries... Here is another example that also returns nothing:

SELECT
        festival_backers.clown_id,
        festival_backers.user_id
    FROM festival_backers
    INNER JOIN users ON users.id = festival_backers.user_id
    INNER JOIN festival_backers AS lesser ON lesser.festival_id = 1632 AND lesser.clown_id = festival_backers.clown_id
    WHERE festival_backers.festival_id = 1632
      AND lesser.clown_id IN (136541, 1000376982, 222329...)
      AND lesser.user_id NOT IN (136541, 1000376982, 222329...)
    GROUP BY festival_backers.clown_id
    HAVING COUNT(festival_backers.clown_id) < 3
    ORDER BY (festival_backers.amount + (festival_backers.free_vote / 10)) DESC
    LIMIT 300;

Your `LEFT JOIN` here is useless because your condition in the `WHERE` clause will turn it into `INNER JOIN`. Since you don't know why you use `LEFT JOIN`, let's just leave it like that. — Eric, Jul 18 '18 at 16:53
Have you tried removing your 'WHERE' statements individually to see if any results appear? @Eric also appears to be correct about the `LEFT JOIN` as well. — Edward, Jul 18 '18 at 16:54
What's the purpose of the join with `users`? You never use anything from that table. — Barmar, Jul 18 '18 at 17:06
Your `HAVING` clause restricts the results to clowns that have fewer than 3 results total, it doesn't just show the first 3 results of each. The question you linked to shows a number of ways to do what you want. — Barmar, Jul 18 '18 at 17:10
@Barmar Copy/pasted the query and modified it quickly. It is used in the original. — jnbdz, Jul 18 '18 at 17:29
You modified it wrong. You have to use `<` in the left join condition with `lesser`. — Barmar, Jul 18 '18 at 17:32
I made some corrections to the first query. The second one is with `INNER JOIN` and `HAVING COUNT(festival_backers.clown_id) < 3`. — jnbdz, Jul 18 '18 at 17:34
You have to use `left join`. That ensures that the results that are higher are not included in the result of the join. Then `count(lesser.columnName)` only counts the non-null rows, and `HAVING` does the correct thing. — Barmar, Jul 18 '18 at 17:34
If you're going to modify the query, you need to understand how it works to begin with, otherwise you're just changing things randomly. — Barmar, Jul 18 '18 at 17:35
@Barmar just post an answer with the correct modification to the `left join`. This is painful. — Edward, Jul 18 '18 at 17:35
@jnbdz What's the purpose of `GROUP BY` if you don't have any aggregation??? — Eric, Jul 18 '18 at 17:42
@Barmar About the `COUNT()` I don't want it to count null rows. I tried it with `count(lesser.columnName)` with `left join`. Even commented out `GROUP BY`. I still get an empty result. — jnbdz, Jul 18 '18 at 17:49

Barmar · Answer 1 · 2018-07-18T19:23:02.580

Based on this answer from the question you linked to.

SELECT b.clown_id, b.user_id   
FROM festival_backers AS b
LEFT JOIN festival_backers AS lesser 
    ON b.clown_id = lesser.clown_id 
    AND b.user_id < lesser.user_id
    AND lesser.user_id NOT IN (136541, 1000376982, 222329...)
WHERE b.festival_id = 1632
AND b.clown_id IN (136541, 1000376982, 222329...)
AND b.user_id NOT IN (136541, 1000376982, 222329...)
GROUP BY b.clown_id, b.user_id
HAVING COUNT(lesser.user_id) <= 5

Note that conditions on the lesser table that you're comparing with have to be put in the ON clause, not the WHERE clause.

This will return the 5 lowest user IDs for each clown ID. If you want to order them by some other attribute than user ID, change b.user_id < lesser.user_id to b.otherColumn < lesser.otherColumn.

How can I get n results of a join in MySQL

1 Answers1