Is it possible to construct a single query to match two tables enforcing a 1:1 relationship between them?
===

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
	UserID			Integer
	ProductID		Integer
	Quantity		Integer

	Gifts
	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