多个数据库联接 [英] Multiple database joins

问题描述

我有三个表:categories，content_info和content.

• categories表包含类别的id和其parent类别的ID.
• content_info包含两列:entry_id表示帖子的ID，而cat_id表示帖子的类别的ID.
• content表包含有关帖子的多个列-例如ID，title等.

• The categories table contains the category's id and the ID of its parent category.
• The content_info contains two columns: entry_id for the post's ID and cat_id for the ID of the post's category.
• The content table contains multiple columns about the post - such as ID, title, etc.

我在URL中有一个名为parent_id的变量，它对应于类别的parent.我想列出所有具有parent_id值parent类别的帖子(而不是类别).

I have a variable in the URL called parent_id which corresponds to the parent of a category. I want to list all the POSTS (not CATEGORIES) which belong to a category with a parent of the parent_id value.

例如，假设parent_id值为5.每个帖子可能属于ID为20的类别，但该类别属于父类别(其ID为5).我想列出所有属于类别的帖子，其parent值等于当前parent_id的值.

For example, say the parent_id value is 5. Each post might belong to a category with an ID of 20, but that category belongs to the parent category (whose ID is 5). I want to list all the posts who belong to categories with a parent value of whatever the current parent_id happens to be.

有没有一种方法可以通过MySQL联接而不是更改PHP?

Is there a way of doing this with MySQL joins instead of changing the PHP?

推荐答案

这应该做到:

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>

这将返回父类别为parent_id

或带有子查询:

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>)

这篇关于多个数据库联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！