spicacms.sql

# Orphan relation in "category_post_rel"
SELECT post_id FROM category_post_rel cp
WHERE NOT EXISTS (SELECT * FROM posts p WHERE cp.post_id = p.post_id)

# Posts that have no category yet
SELECT post_id FROM posts p
WHERE NOT EXISTS (SELECT * FROM category_post_rel cp WHERE cp.post_id = p.post_id)


SELECT p.post_id, p.post_title, p.post_created_dtime, cp.cat_id, cp.post_id -- , c.cat_name
FROM (
    SELECT cat_id, post_id FROM (
	SELECT l.cat_id, l.post_id, COUNT(1) AS rank
	FROM category_post_rel AS l
	LEFT OUTER JOIN category_post_rel AS r
	ON l.cat_id = r.cat_id
	AND l.post_id >= r.post_id
	GROUP BY l.cat_id, l.post_id) r
    WHERE rank <=3) cp
-- left OUTER join categories c
-- ON cp.cat_id = c.cat_id 
LEFT OUTER JOIN posts p
ON p.post_id = cp.post_id
-- AND cp.cat_id = c.cat_id 

# http://explainextended.com/2009/09/14/mysql-emulating-row_number-with-multiple-order-by-conditions/