For those interested in the reasoning behind this question: I have an e-commerce site that works fine, but has no gift certificate capabilities. Adding monetary GCs should be pretty simple, but I'd also like to allow the gifting of specific products (sounds odd but is relevant to my industry). So I plan to create a new table to house gift certificates that are linked to a specific user and product, and I need an efficient way to evaluate that table on the cart and checkout pages.

Imagine tables exist that look similar to the following:

CartContents
CartID          Integer (Unique sequential row identifier)
UserID          Integer
ProductID       Integer
Quantity        Integer

Gifts
GiftID          Integer (Unique sequential row identifier)  
ProductID       Integer
UserID          Integer
Quantity        Integer

This is an overly simplified layout, but demonstrates the idea. The first table lists items in the user's cart; one record per product (though real products will have additional details that may vary). The product table has further attributes on products but I don't list it here for simplicity. The second table is a set of gift certificates, each for a specific product, that have been presented to this user ID.

Table data may look like the following:

CartContents
CartID          UserID      ProductID       Quantity
1               1           1               1
2               1           2               2
3               1           1               2
4               2           3               1

Gifts
ProductID       UserID      Quantity
1               1           1
2               1           1
3               3           1

Is it possible to construct a single query that provides one row per cart item and links the above two tables taking into account that each gift may only link to each cart item once? Or does this need to be handled in a script?

In other words, because user 1 has product 1 in their cart twice, and they have only been promised one free product 1, the query should return a matching Gifts record for cartID 1, but not cartID 3. The query, pulling for user ID 1, would return:

CartID      ProductID       Quantity        unpaidQuantity
1           1               1               0
2           2               2               1
3           1               2               2

Or

CartID      ProductID       Quantity        unpaidQuantity
1           1               1               1
2           2               2               1
3           1               2               1

I realize that the fact that there is more than one 'right' answer to this question raises a red flag. In reality it doesn't matter which cart record each GC is applied to, as the end result (the price) will work out the same. I'm perfectly happy to say the 'first' (lowest cartID) is the one that should be linked.

My assumption is that the database will be far more efficient at this than any script I could write; I'd even be willing to bet there's some crazy type of join I've never heard of specifically designed for it. I am also assuming that any such ColdFusion script may be somewhat complicated and thus take a fair amount of development and testing time while a single query may be relatively simple (though apparently beyond my limited SQL capabilities). If I'm incorrect in this I'd appreciate any thoughts on that as well.

My setup, if it matters:

• MySQL 5.0

• ColdFusion 9

• Windows 2000 AS

Edit: It sounds like the quantity column is really going to cause issues, so let's continue assuming that quantity does not exist on the Gifts table. It still must exist on cartContents, however.