MySql query to select articles from a given category?

Question

This seems like it should be so easy... I have a categories table, that has catid and parent id. If a category is root, then its parent is null. I also have articles in a table with fields id, name, catid.

For instance, there could be category Europe, and subcategories Germany, France. Then in articles table there would be the catid.

I need to do a query so that for a given catid, I get all the articles who have the parent id for that catid. Or, given the parent id a query to likewise give me all articles with that parent id. However, article table only contains catid, not parent id.

I just can't figure out if I need to do one of many variations of joins, unions, etc.

And meant to say should be sorted by article date.

Thanks!

Answer 1

Give this a try

This query will give you all articles with catid=1 and it will also give you all articles there parent_id = 1

SELECT * FROM articles AS a 
WHERE EXISTS(SELECT 1 FROM categories WHERE catid =  a.catid OR parent_id = a.catid)
AND a.catid = 1

Answer 2

Assume that the catid you are querying for is 5.

SELECT articles.* FROM categories, articles WHERE categories.parentid = 5 AND articles.catid = categories.catid

This simple JOIN query should give you all the articles that belong to the direct descendant categories of parentid.

This article might help: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/