I'm getting ever more complicated in my MySQL queries and pushing the boundaries of my fairly-limited knowledge. Here's the scenario I'm facing now:

I have 3 tables: customer, customer_website, and customer_contract.

The relationships are:

1 customer has 1 or more customer_website entries.
1 customer_website has 1 or more customer_contract entries.

customer_website contains a foreign key to customer.
customer_contract contains a foreign key to customer_website.

When the customer approves a contract I'm trying to ensure that they can't hack the system to approve a contract which isn't theirs, so I'm constructing a query of this form:

UPDATE customer_contract SET approved='Yes' WHERE customer_website=1 AND XXX

I want XXX to be filled in with this pseudocode:

WHERE customer_website=1 THEN customer=2

But, since it's not concerning the updated table, I've confused myself.

I believe that that is sufficient information to assist, but let me know if you need anything else.

Oh, and the DB is in 3NF, so customer is not a foreign key in customer_contract.

Thanks in advance!

So you can use CASE WHEN X = Y THEN Z ELSE A END syntax, but I don't see how that really gets you what you want. It seems like a correlated subquery might be somewhat better, but I'm again not really sure.

How would you know if the system was hacked, or, if the person sending the input (or queries) represented the customer they claimed to? There's no way to have that information in your SQL, so there isn't much you can do to address it.

A foreign key would prevent a row from going into your contract table for a CustomerID that doesn't exist in your customer table. It could also prevent a Customer_Contract row that points to a Customer_Website row that doesn't exist, so CustomerID and WebSiteID would both have to be valid. That still doesn't seem to buy you very much.

Writing a correlated subquery would let you consider more data, like the value of approved. I think you're still missing something you need, tho.

What values are possible in approved? If it's only yes or no, bit would be a better data type.

I'm using the MyISAM table format so foreign keys aren't enforced.

What's a "correlated subquery"?

As far as hacking goes, here's how I would hack the system:

The customer_contract id is passed via a hidden form field (easy enough to change). This is done b/c the user may have multiple windows open viewing many different contracts, so I can just set it in a session variable.

The user id, however, is in the session variables, so it's easy enough to protect from hacking.

approved is of type ENUM('Yes','No') default 'NO', so it only takes up 1 byte as would a TINYINT(1) -- enum just makes it all more readable. But let me know if the thinking is wrong b/c that's how I do all of my binary values.

And, thanks a ton for your help!

I'm thinking that instead of the query validation, that I could hash the customer_contract id with a seed and validate the hash if I can't figure out the query.

I'm very interested in the "correlated subquery" option, however.

I would lean toward hashing, encrypting, or somehow munging the value in your hidden form field, like you suggested. There are so many ways to do that, you have your pick. That's news to me, that MySQL will store enums in 1 byte. I can imagine how it's possible (lookup "table" and a 1-byte int yielding 256 values), it's just not an option we have without making a 2nd table in SQL Server, so it stood out to me as something to suggest - but it sounds like you ought to ignore me on this one. In MS, if we use a bit field, 1 of them = 1 byte, but 2 bit fields also = 1 byte, up to 8. So we tend to prefer those when possible, and you and I have been talking deeper level implementation.

Anyway, a correlated subquery is when you have an "outer" and an "inner" query that are correlated. The inner query filters which rows pass through the outer query, and the outer query shapes what the inner query looks like. I don't understand your schema, so I'll make some assumptions that will probably be wrong.

UPDATE customer_contract As C SET approved='Yes' WHERE customer_website=1 AND customerID In (Select customerID From customer_account A Join customer_website W On A.ID = W.ID)

I don't think that's even close to your intention. A simpler but more abstract example is

Select * From Order_Header Where Order_Total > (Select Avg(Order_Total) From Order_Header)

That one isn't correlated - there's a 1 way link. The inner subquery will limit what comes out of the outer query, but the outer query doesn't drive what the inner query considers. Actually, that might be a better match than, say, if you changed the query I just posted to only find the average for customers in the same state as that order.

Select * From Order_Header OH Where Order_Total > (Select Avg(Order_Total) From Order_Header IH Where OH.State = IH.State)

Here's from MSDN

Correlated Subqueries

Correlated subqueries involve two queries which are mutually dependent. The values from the "outer" query feed into the "inner" query, which in turn determines the results of the "outer" query. Correlated subqueries have many applications outside of the scope of this page. For more information see the links at the bottom of this page. One (of many) general examples of this query is as follows:

SELECT <Column List>
FROM MyTable AS a
WHERE NOT EXISTS (SELECT Column1
FROM MyOtherTable AS b
WHERE a.KeyColumn = b.KeyColumn)

The following example uses AdventureWorks to demonstrate a correlated subquery:

USE AdventureWorks
GO
SELECT c.CustomerID
FROM Sales.Customer AS c
WHERE NOT EXISTS
( SELECT sh.CustomerID
FROM Sales.SalesOrderHeader AS sh
WHERE c.CustomerID = sh.CustomerID
AND sh.OrderDate BETWEEN '2004-01-01' AND GETDATE())

Caution: Under some circumstances, a correlated subquery can result in poor query performance (the solution is less of a pure set solution than the other examples on this page. As with any query, test each option and analyze the query plans under realistic test circumstances prior to deciding on a strategy.

OH! I've always called those nested queries. Vocabulary, eh? I was thinking about the nested query here, but since the field isn't in the updated table, I got lost. Your examples do clear things up substantially so that I could now tweak your first example to get what I need.

Given what you've provided here and the fact that the DB must cross reference that other table, I'm thinking I'll just go with the hash method b/c those are hyperfast comparatively speaking.

For clarity's sake for any reading this, ENUMs are stored, according to http://dev.mysql.com/doc/refman/5.1/...html#id3279661 , as 1 or 2 bytes depending on the number of of values allowed.

I always appreciate your advice, John. Thanks again! I think I've given you all the TK I can for awhile.