CODEWITHSUNDEEP
mysqlsql
C-Rad
C-Rad

Reputation: 355

MYSQL join/union with conditional limit

In this query I want a total of x number of records returned. within that query I have several sub-queries where I can't be sure if they'll return the max number of records. if one result is less than it's max limit I want to populate the remaining slots with the next query and so on. I can't do math inside a limit clause so I'm still trying to figure out how to do it. here is what I would do if math was available inside the limit clause.

select *
from 
(
(select * from profile where size='local' order by rand() limit 7) as local 
join 
(select * from profile where size='regional' order by rand() limit (13-count(local.id)) as regional
join 
(select * from profile where size='national' order by rand() limit (19-(count(local.id)+count(regional.id))) as national
join 
(select * from profile where size='international' order by rand() limit (25-(count(local.id)+count(regional.id)+count(national.id)))) as international    
)

Upvotes: 1

Views: 235

Answers (1)

Kickstart
Kickstart

Reputation: 21533

I might have done this a needlessly complicated way, but it does seem to work:-

SELECT id, size
FROM
(
    SELECT id, size, 
        @SeqLocal:=IF(size="local", IF(@SeqLocal <= 7, @SeqLocal + 1, @SeqLocal), @SeqLocal) AS SeqLocal,
        @SeqRegional:=IF(size="regional", IF(@SeqLocal + @SeqRegional <= 14, @SeqRegional + 1, @SeqRegional), @SeqRegional) AS SeqRegional,
        @SeqNational:=IF(size="national", IF(@SeqLocal + @SeqRegional + @SeqNational <= 21 , @SeqNational + 1, @SeqNational), @SeqNational) AS SeqNational,
        @SeqInternational:=IF(size="international", IF(@SeqLocal + @SeqRegional + @SeqNational + @SeqInternational <= 28, @SeqInternational + 1, @SeqInternational), @SeqInternational) AS SeqInternational
    FROM
    (
        select *
        from profile 
        where size IN ("local", "regional", "national", "international")
        order by FIELD(size, "local", "regional", "national", "international"), rand() 
    ) Sub1
    CROSS JOIN (SELECT @SeqLocal:=0, @SeqRegional:=0, @SeqNational:=0, @SeqInternational:=0) Sub2
) Sub3
WHERE (size = "local" AND SeqLocal != @SeqLocal)
OR  (size = "regional" AND SeqRegional != @SeqRegional)
OR  (size = "national" AND SeqNational != @SeqNational )
OR  (size = "international" AND SeqInternational != @SeqInternational)

Sqlfiddle here:-

http://www.sqlfiddle.com/#!2/cc3b884/14

Upvotes: 1

Related Questions