I've three tables:
categoriestable consists of the category's
idand also the ID of their
content_infoconsists of two posts:
entry_idfor that post's ID and
cat_idfor that ID from the post's category.
contenttable consists of multiple posts concerning the publish - for example
I've got a variable within the URL known as parent_id which matches the
parent of the category. I wish to list all of the POSTS (not Groups) which fit in with a category having a
parent from the parent_id value.
For instance, the parent_id value is 5. Each publish might fit in with a category by having an ID of 20, but that category goes towards the parent category (whose ID is 5). I wish to list all of the posts who fit in with groups having a
parent worth of regardless of the current parent_id is actually.
It is possible to method of carrying this out with MySQL joins rather than altering the PHP?
This will get it done:
SELECT c.* FROM content JOIN content_info ci ON ci.entry_id=c.id JOIN categories cat ON cat.id=ci.cat_id WHERE cat.parent_id=<parent_id>
This return all posts (
content rows) which fit in with a category which parent is
Or with subqueries:
SELECT c.* FROM content JOIN content_info ci ON ci.entry_id=c.id WHERE ci.cat_id IN (SELECT id FROM categories cat WHERE cat.parent_id=<parent_id>)
SELECT c.* FROM content c, categories cat, content_info ci WHERE c.id = ci.entry_id AND cat.id = ci.cat_id AND cat.parent_id = 5