CODEWITHSUNDEEP
sqlmysqlcoldfusion
Mohamad
Mohamad

Reputation: 35349

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

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)

Upvotes: 0

Views: 2353

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332681

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.

Upvotes: 4

Related Questions