How to write a Nested SQL Query
I am trying to filter search results into 2 separate results on one page. Eg: I have paid customers and free customers, I want the paid customers results to be at the top of the page BUT I want them to be ORDER BY RAND() and the free customers results below the paid customers ORDER BY name ASC. I have tried to achieve this by a nested SQL Query, eg:
$rsResult->setQuery("SELECT * FROM (SELECT bID, Name)AS paid FROM directory WHERE Category = ? AND Link = 1 AND Active = 1, (SELECT bID, Name)AS free FROM directory WHERE Category = ? AND Link = 0 AND Active = 1 ORDER BY free ASC, paid RAND()");
Would it be easier to just create 2 separate recordsets to achieve this?