Some of these and maybe some of those

[thedaver]

Having trouble thinking through a problem...

I want to retrieve exactly four items from a pool of ~100 records where each record has a keyword field with many keywords that might lead me to match that record. (easy so far)

I want to retrieve a random list of up to four records found by keyword matches in any order. (still easy using 'order by random()'.

(now comes the icky bit)

If I retrieve less than four records from the keyword match, I want to pull the balance of the four needed records randomly from the whole set to complete my set of four.

I think a 'union' or 'join' is needed, but I'm not sure. I know this can be done with two SQL statements and just folding the result sets together in code. BUT... I'd really like to make MySQL do all the work and return the set in one pass. (read: efficiency of a single call to Mysql)

Am I off my rocker?
Thanks!

 

[KarveR]

one call can be waaaaaaaay more resource hungry than two well formed queries. ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.