MySQL: in designing a loot drop table, is it possible to specify a number of times the query repeats itself and outputs each result on the same table

as part of teaching myself SQL, I'm coding a loot drop table that I hope to use in D&D campaigns.

the simplest form of the query is:

SELECT rarity,
     CASE
        WHEN item=common THEN (SELECT item FROM common.table)
        WHEN item=uncommon THEN (SELECT item FROM unommon.table)
        ...etc
     END AS loot
 FROM rarity.table
ORDER BY RAND()*(1/weight)
LIMIT 1

the idea is that the query randomly chooses a rarity from the rarity.table based on a weighted probability. There are 10 types of rarity, each represented on the rarity.table as a single row and having a column for probabilistic weight.

If I want to randomly output 1 item (limit 1), this works great.

However, attempting to output more than 1 item at a time isn't probabilistic in that the query can only put out 1 row of each rarity. If say I want to roll 10 items (limit 10) for my players, it will just output all 10 rows, producing 1 item from each rarity, and never multiple of the higher weighted rarities.

I have tried something similar, creating a different rarity.table that was 1000 rows long, and instead of having a 'weight' column representing probabilistic weight in rows, ex. common is rows 1-20, uncommon rows 21-35...etc. Then writing the query as

ORDER BY RAND()
LIMIT x

-- (where x is the number of items I want to output)

and while this is better in some ways, it results are still limited by the number of rows for each rarity. I.E. if I set limit to 100, it again just gives me the whole table without taking probability into consideration. This is fine in that I probably won't be rolling 100 items at once, but feels incorrect that the output will always be limited to 20 common items, 15 uncommon, etc. This is also MUCH slower, as my actual code has a lot of case and sub-case statements.

So, my thought moved on to if is possible to run the query with a limit 1, but to set the query to run x number of times, and then include each result on the same table, preserving probability and not being limited by the number of rows in the table. However, I haven't figured out how to do so.

Any thoughts on how to achieve these results? Or maybe a better approach? Please let me know if I can clarify anything.

Thank you!