MySQL query to get “intersection” of numerous queries with limits

Flatten Your Criteria

You can flatten your multi-dimensional criteria into a single level criteria

Now this criteria can be achieved in one query as follow

(SELECT * FROM users WHERE gender="Male" AND region = 'North' LIMIT 40) UNION ALL
(SELECT * FROM users WHERE gender="Male" AND region = 'South' LIMIT 80) UNION ALL
(SELECT * FROM users WHERE gender="Male" AND region = 'East' LIMIT 120) UNION ALL
(SELECT * FROM users WHERE gender="Male" AND region = 'West' LIMIT 160) UNION ALL
(SELECT * FROM users WHERE gender="Female" AND region = 'North' LIMIT 60) UNION ALL
(SELECT * FROM users WHERE gender="Female" AND region = 'South' LIMIT 120) UNION ALL
(SELECT * FROM users WHERE gender="Female" AND region = 'East' LIMIT 180) UNION ALL
(SELECT * FROM users WHERE gender="Female" AND region = 'West' LIMIT 240)

Problem

• It does not always return the correct result. For example, if there are less than 40 users whose are male and from north, then the query will return less than 1,000 records.

Adjust Your Criteria

Let say that there is less than 40 users whose are male and from north. Then, you need to adjust other criteria quantity to cover the missing quantity from “Male” and “North”. I believe it is not possible to do it with bare SQL. This is pseudo code that I have in mind. For sake of simplification, I think we will only query for Male, Female, North, and South

conditions.add({ gender: 'Male',   region: 'North', limit: 40  })
conditions.add({ gender: 'Male',   region: 'South', limit: 80  })
conditions.add({ gender: 'Female', region: 'North', limit: 60  })
conditions.add({ gender: 'Female', region: 'South', limit: 120  })

foreach(conditions as condition) {
    temp = getResultFromDatabaseByCondition(condition)
    conditions.remove(condition)

    // there is not enough result for this condition,
    // increase other condition quantity
    if (temp.length < condition.limit) {
        adjust(...);
    }
}

Let say that there are only 30 northener male. So we need to adjust +10 male, and +10 northener.

To Adjust
---------------------------------------------------
Male        +10
North       +10

Remain Conditions
----------------------------------------------------
{ gender: 'Male',   region: 'South', limit: 80 }
{ gender: 'Female', region: 'North', limit: 60  }
{ gender: 'Female', region: 'South', limit: 120  }

‘Male’ + ‘South’ is the first condition that match the ‘Male’ adjustment condition. Increase it by +10, and remove it from the “remain condition” list. Since, we increase the South, we need to decrease it back at other condition. So add “South” condition into “To Adjust” list

To Adjust
---------------------------------------------------
South       -10
North       +10

Remain Conditions
----------------------------------------------------
{ gender: 'Female', region: 'North', limit: 60  }
{ gender: 'Female', region: 'South', limit: 120  }

Final Conditions
----------------------------------------------------
{ gender: 'Male',   region: 'South', limit: 90 }

Find condition that match the ‘South’ and repeat the same process.

To Adjust
---------------------------------------------------
Female      +10
North       +10

Remain Conditions
----------------------------------------------------
{ gender: 'Female', region: 'North', limit: 60  }

Final Conditions
----------------------------------------------------
{ gender: 'Female', region: 'South', limit: 110  }
{ gender: 'Male',   region: 'South', limit: 90 }

And finally

{ gender: 'Female', region: 'North', limit: 70  }
{ gender: 'Female', region: 'South', limit: 110  }
{ gender: 'Male',   region: 'South', limit: 90 }

I haven’t come up with the exact implementation of adjustment yet. It is more difficult than I have expected. I will update once I can figure out how to implement it.