如何比较两个查询的结果，并找到两者之间的匹配项?

First of all, I'm not sure if this should be done in Coldfusion or MySQL.

首先，我不确定这应该在Coldfusion还是MySQL中完成。

I have a query of items that retrieves items in a catalog. I have a second query that retrieves items from a user's list. My objective is to find out if an item is already present in a user's list when outputting the first query (the items catalog).

我有一个检索目录中的项的查询。我有第二个查询从用户列表中检索项目。我的目标是在输出第一个查询(项目目录)时，找出用户列表中是否已经有一个项目。

items(itemId, itemName)

users_items(itemId,memberId)

1 个解决方案

#1

Can an item belong to more than one catalog? If so, you can't tell which catalog based on the USERS_ITEMS table to render the list properly.

一个产品可以属于多个目录吗?如果是这样，您就无法判断基于USERS_ITEMS表的哪个目录来正确地呈现列表。

Otherwise, I think you could do with using a LEFT JOIN:

否则，我认为您可以使用左连接:

   SELECT i.itemid,
          i.itemname,
          ui.memberid
     FROM ITEMS i
LEFT JOIN USERS_ITEMS ui ON ui.itemid = i.itemid
                        AND ui.memberid = ?

...which'll return a result like (I omitted itemname):

…它将返回如下结果(我省略了itemname):

itemid   memberid
--------------------
1        1234
2        NULL
3        1234

Where you see NULL, tells you that the member hasn't ordered the item.

当你看到NULL时，它告诉你这个成员没有对这个项目下命令。

In Coldfusion, you just have to setup the page to handle the add or remove option appropriately based on the presence of a value or NULL.

在Coldfusion中，您只需设置页面，根据值或NULL的存在适当地处理添加或删除选项。

only allow someone to "add to list" when the memberid is null (IE: item 2)
只有当memberid为空时才允许某人“添加到列表”(即:item 2)
if memberid is not null (IE items 1 & 3) --provide the "remove from list" option.
如果memberid不是null(即项目1和3)——提供“从列表中删除”选项。

#1