Purpose: Every half-hour I query database to locate a publish that havn't released to write,each and every time I query by different category,if no posts within this category,change to next category. The if check made by SQL

SELECT FROM post WHERE post.cat_ID='7' AND post_date > '$last_query_time_cat_ID_7' 
IF NO_POSTS_MATCH SELECT FROM post WHERE post.cat_ID='8' AND post_date>'$last_query_time_cat_ID_8'

pseudo explain:choose one publish of 1 category by its last query duration of this category,if no new publish of the category choose from another category by its last query time.
My question:
Can there be such SQL to get this done work?
You can do this by php to see if no posts return,make another sql query.This might make many queries.
Somebody show me the sql-pseudo-code of the problem?

Use Situation..WHEN...ELSE Statement.. Begin to see the link: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Could seem like this:

SELECT p.post_ID, p.cat_ID, p.post_date
FROM   post p
JOIN   (
    SELECT cat_ID
          ,max(query_time) AS last_query
    FROM   post 
    WHERE  cat_ID >= 7
    GROUP  BY 1
    ORDER  BY 1) l USING (cat_ID)
-- in some RDBMS you need ON l.cat_ID = p.cat_ID in the JOIN clause
WHERE  p.post_date > l.last_query
ORDER  BY p.cat_ID, p.post_date DESC
LIMIT  1;  -- In T-SQL you need to write "SELECT TOP 1" instead

Major points

  • First obtain the timestamp from the last query per category inside a subquery. We're only thinking about cat_ID >= 7

  • Join this towards the base table and filter only rows having a later post_date than last_query per cat_ID.

  • Sort it by cat_ID - you appear to wish littlest cat_ID first. Secondary sort by post_date climbing down, therefore the last publish comes first.

  • If you will find none in cat_ID 7, cat_ID 8 is going to be first etc. Voila.