Force Union to avoid getting a duplicate result which is then removed?

Question

I have a query which is as follows:

(SELECT cr.id, cr.price, cd.times, cd.name, cr.rarity 
        FROM rarity_db cr
        JOIN card_db cd
        ON cr.id = cd.id
        WHERE cr.rarity = 'Common'
        AND cr.set_code like '%lob%' 
        GROUP BY cd.id ORDER BY RAND() LIMIT 7)

        UNION

        (SELECT cr.id, cr.price, cd.times, cd.name, cr.rarity 
        FROM rarity_db cr
        JOIN card_db cd
        ON cr.id = cd.id
        WHERE cr.rarity = 'Rare'
        AND cr.set_code like '%lob%'
        GROUP BY cd.id ORDER BY RAND() LIMIT 1)

        UNION

        (SELECT cr.id, cr.price, cd.times, cd.name, cr.rarity 
        FROM rarity_db cr
        JOIN card_db cd
        ON cr.id = cd.id
        WHERE cr.rarity != 'Rare'
        AND cr.set_code like '%lob%'
        GROUP BY cd.id ORDER BY RAND() LIMIT 1)

The logic for the third query is that there is a chance it can either be a common or a rarity that is higher than rare. Currently it's a random chance so the sequence would be as follows:

• 7 Commons
• 1 Rare
• Either 1 Common or a Rarity higher than Rare

Because I am using UNION, duplicates are stripped. I know I can use UNION ALL to keep duplicates but the issue is that I need to have no duplicates without anything being stripped out.

I absolutely need 9 results but with the above query I can occasionally get 8 since the last query can get one of the same results that was pulled from the first 7.

Is there a pure SQL method of getting around this or will I have to resort to PHP?

From a PHP standpoint, I could execute the first two queries, push all the results from the first into an array and then execute the third query and tell it to also avoid any results stored in said array.

That's the only workaround I've been able to come up with so far.

MySQL version: 5.6.45-cll-lve

Answer 1

I think the following could work (it should be a sql equivalent to the idea I posted in the comments above):

    SELECT * FROM( 

    (SELECT cr.id, cr.price, cd.times, cd.name, cr.rarity 
    FROM rarity_db cr
    JOIN card_db cd
    ON cr.id = cd.id
    WHERE cr.rarity != 'Rare'
    AND cr.set_code like '%lob%'
    GROUP BY cd.id ORDER BY RAND() LIMIT 1)

    UNION 

    (SELECT cr.id, cr.price, cd.times, cd.name, cr.rarity 
    FROM rarity_db cr
    JOIN card_db cd
    ON cr.id = cd.id
    WHERE cr.rarity = 'Rare'
    AND cr.set_code like '%lob%'
    GROUP BY cd.id ORDER BY RAND() LIMIT 1)

    UNION    

    (SELECT cr.id, cr.price, cd.times, cd.name, cr.rarity 
    FROM rarity_db cr
    JOIN card_db cd
    ON cr.id = cd.id
    WHERE cr.rarity = 'Common'
    AND cr.set_code like '%lob%' 
    GROUP BY cd.id ORDER BY RAND() LIMIT 8) -- Set this limit to 8

    ) AS t LIMIT 9 -- then take first 9

Edit: updated it since my previous version could still fail and return 8 results only

You can then reverse the array in PHP if you need it in the specific order of your original query