如何使用 MySQL 关联表返回当前未分配给条目的类别 [英] How to use an MySQL association table to return categories not currently assigned to an entry

问题描述

我有一个产品条目数据库.每个产品条目可以与多个类别相关联.我创建了一个关联表来创建这些关联.我想要做的是创建一个 MySQL 查询，该查询将返回当前未分配给该条目的所有类别.

I have a database of product entries. Each product entry can be associated with multiple categories. I have created an association table to create these associations. What I am trying to do is create a MySQL query that will return all categories that are not currently assigned to the entry.

因此，如果条目是台球桌并且已分配给游戏类别，我想创建一个查询，该查询将返回除游戏之外的所有类别.

So if an entry is a Pool Table and it is already assigned to the Gaming category, I want to create a query that will return all categories except Gaming.

以下是我的数据库的表结构.任何帮助将不胜感激.

Below are table structure for my database. Any help would be greatly appreciated.

输入表:

+--------------+------------------+
| Field        | Type             |
+--------------+------------------+
| eid          | int(10) unsigned |
| entry_name   | varchar(50)      |
+--------------+------------------+

类别表:

+----------------+------------------------+
| Field          | Type                   |
+----------------+------------------------+
| cid            | smallint(11) unsigned  |
| cat_name       | varchar(30)            |
+----------------+------------------------+

关联表:

+-------+-----------------------+
| Field | Type                  |
+-------+-----------------------+
| eid   | int(10) unsigned      |
| cid   | smallint(11) unsigned |
+-------+-----------------------+

推荐答案

我能够从 nick rulez 的提示中弄清楚.

I was able to figure it out with the tip from nick rulez.

此查询返回尚未分配给特定条目的类别.在此示例中，条目 ID 为 493:

This query returns the categories that have not been assigned to a particular entry. In this example, the entry id is 493:

select c.* from category c left join Association a on a.pid = c.pid and a.eid = '493' where a.eid is NULL

不确定这是否是最有效的方法，但确实有效.

Not sure if this is the most efficient way to do it, but it does work.

这篇关于如何使用 MySQL 关联表返回当前未分配给条目的类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！