MYSQL - Combining Two Results in One Query

Question

I have a query I need to perform to show search results for a project. What needs to happen, I need to sort the results by the "horsesActiveDate" and this applies to all of them except for any ad with the adtypesID=7. Those results are sorted by date but they must always result after all other ads.

So I will have all my ads in the result set be ordered by the Active Date AND adtypesID != 7. After that, I need all adtypesID=7 to be sorted by Active Date and appended at the bottom of all the results.

I'm hoping to put this in one query instead of two and appending them together in PHP. The way the code is written, I have to find a way to get it all in one query.

So here is my original query which has worked great until I had to ad the adtypesID=7 which has different sorting requirements.

This is the query that exists now that doesn't take into account the adtypesID for sorting.

SELECT 
    horses.horsesID, 
    horsesDescription, 
    horsesActiveDate, 
    adtypesID, 
    states.statesName, 
    horses_images.himagesPath 

FROM horses 

LEFT JOIN states ON horses.statesID = states.statesID 
LEFT JOIN horses_images ON horses_images.himagesDefault = 1 AND horses_images.horsesID = horses.horsesID AND horses_images.himagesPath != '' 

WHERE 
        horses.horsesStud = 0 
    AND horses.horsesSold = 0 
    AND horses.horsesID IN 
        ( 
             SELECT DISTINCT horses.horsesID 
             FROM horses 
             LEFT JOIN horses_featured ON horses_featured.horsesID = horses.horsesID   
             WHERE horses.horsesActive = 1 
        )

ORDER BY adtypesID, horses.horsesActiveDate DESC

My first thought was to do two queries where one looked for all the ads that did not contain adtypesID=7 and sort those as the query does, then run a second query to find only those ads with adtypesID=7 and sort those by date. Then take those two results and append them to each other. Since I need to get this all into one query, I can't use a php function to do that.

Is there a way to merge the two query results one after the other in mysql? Is there a better way to run this query that will accomplish this sorting?

The Ideal Results would be as below (I modified the column names so they would be shorter):

   ID  | Description  | ActiveDate  |  adtypesID  | statesName  | himagesPath
    ___________________________________________________________________________

   3    |  Ad Text     |  06-01-2010 |  3          | OK          |  image.jpg
   2    |  Ad Text     |  05-31-2010 |  2          | LA          |  image1.jpg
   9    |  Ad Text     |  03-01-2010 |  4          | OK          |  image3.jpg
   6    |  Ad Text     |  06-01-2010 |  7          | OK          |  image5.jpg
   6    |  Ad Text     |  05-01-2010 |  7          | OK          |  image5.jpg
   6    |  Ad Text     |  04-01-2010 |  7          | OK          |  image5.jpg

Any help that can be provided will be greatly appreciated!

Answer 1

This should work:

ORDER BY (adtypesID = 7) ASC, horses.horsesActiveDate DESC

Answer 2

I am not sure about the exact syntax in MySQL, but something like

ORDER BY case when adtypesID = 7 then 2 else 1 end ASC, horses.horsesActiveDate DESC

would work in many other SQL dielects. Note that most SQL dialects allow the order by to not only be a column, but an expression.

Answer 3

Use a Union to append two queries together, like this:

SELECT whatever FROM wherever ORDER BY something AND adtypesID!=7

UNION

SELECT another FROM somewhere ORDER BY whocares AND adtypesID=7

http://dev.mysql.com/doc/refman/5.0/en/union.html

Answer 4

I re-wrote your query as:

   SELECT h.horsesID, 
          h.horsesDescription, 
          h.horsesActiveDate, 
          adtypesID, 
          s.statesName, 
          hi.himagesPath 
     FROM HORSES h
LEFT JOIN STATES s ON s.stateid = h.statesID
LEFT JOIN HORSES_IMAGES hi ON hi.horsesID = h.horsesID 
                          AND hi.himagesDefault = 1 
                          AND hi.himagesPath != ''
LEFT JOIN HORSES_FEATURED hf ON hf.horsesID = h.horsesID   
    WHERE h.horsesStud = 0 
      AND h.horsesSold = 0
      AND h.horsesActive = 1
 ORDER BY (adtypesID = 7) ASC, h.horsesActiveDate DESC

The IN subquery, using a LEFT JOIN and such, will mean that any horse record whose horsesActive value is 1 will be returned - regardless if they have an associated HORSES_FEATURED record. I leave it to you for checking your data to decide if it should really be an INNER JOIN. Likewise for the STATES table relationship...