How do I compare the results of two queries, and find matches between the two?

Question

EDIT: Changed the question to be more clear.

EDIT 2: The intention here to create an "add to list" and "remove from list" when outputting a catalog (I don't want a user to be able to add an item more than once)

First of all, I'm not sure if this should be done in Coldfusion or 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)

Answer 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.

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

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

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

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.

• only allow someone to "add to list" when the memberid is null (IE: item 2)
• if memberid is not null (IE items 1 & 3) --provide the "remove from list" option.