MySQL加入3个表的把戏 [英] MySQL join with 3 tables trick

问题描述

我无法获得想要的结果，请让我解释一下:

I can't obtain the result I want, let me explain:

我有两个表:

• 类别
• 项目

• CATEGORIES
• PROJECTS

以及在它们之间的另一个建立链接的表:

and between them another table which makes the link:

• ASSIGNED_CAT ，其中有两列:PROJECT_ID和CATEGORY_ID

• ASSIGNED_CAT with 2 columns: PROJECT_ID and CATEGORY_ID

在ASSIGNED_CAT中，我获得了CATEGORIES和PROJECTS的ID链接在一起.

In ASSIGNED_CAT I get the IDs of CATEGORIES and PROJECTS linked together.

现在，我需要获取包含项目的类别，如果没有显示，则不显示它们.

Now I need to get the CATEGORIES which contains PROJECTS and if not don't show them.

示例:
如果CATEGORY 1有项目，则显示CATEGORY NAME，否则不显示.

Example:
If CATEGORY 1 have projects display the CATEGORY NAME and if not don't show.

有什么办法欺骗这个吗?我尝试了许多SQL JOIN都没有成功.

Any idea to trick this? I've tried many SQL JOIN without success.

推荐答案

我想您缺少

I guess you are missing the EXISTS clause.

以下查询选择相交表中至少存在一行的所有类别.

The following query selects all categories, for which at least one row exists in the intersection table.

SELECT category_name
FROM categories c
WHERE EXISTS (
    SELECT 1
    FROM assigned_cat ac
    WHERE ac.category_id = c.category_id
)

这篇关于MySQL加入3个表的把戏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！