SELECT * WHERE else IF ROWS LESS than 20 SELECT something else

[jimmyoneshot]

I'm not sure if this query is possible. I need to select all members of staff from a database that are teachers. However I need it so that if less than 20 records are produced as the result of the query the remaining records will be made up of other regular members of staff to bring it up to 20.

 

For example if I use SELECT * FROM staff WHERE IS_TEACHER = 1 to get all the teachers and it only returns 17 results then I want 3 more regular members of staff to be added into the results. The idea being that 20 results are always shown preferably 20 teachers but if less the remainder will be filled in with regular staff.

 

Is this doable in one query?

[ignace]

You can use UNION to join two results:

 

((SELECT * FROM staff WHERE IS_TEACHER = 1 LIMIT 20)
UNION
(SELECT * FROM staff WHERE IS_TEACHER = 0 LIMIT 20))
LIMIT 20

 

This will select 20 teachers (if available) and 20 regular members (in case there are no teachers otherwise lower this number) and limits the entire result to 20 items. Thus if you have 17 teachers it will add 20 regulars and cut if off at 20 leading towards 17 teachers and 3 regulars.

 

Post your code if you need more help.

[jimmyoneshot]

Thanks for the help Ignace.

 

It seems to be throwing an error though:-

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT * FROM staff WHERE IS_TEACHER = 0 LIMIT 20))LIMIT 20' at line 1

 

Is this something to do with the parentheses?

[ignace]

(SELECT * FROM staff WHERE IS_TEACHER = 1 LIMIT 20)
UNION ALL
(SELECT * FROM staff WHERE IS_TEACHER = 0 LIMIT 20)
LIMIT 20

 

I typed a ( and ) too many. This should work for you.