LEFT JOIN with third-table key

[Jerry Schwartz]

This is a question that I run into frequently; I might even have posted it
before.

If I have three tables:

A: pub_product_id
B: product_id, publisher_id, pub_product_id
C: publisher_id, publisher_code
D: product_id, product_price

and I want to find those `pub_products` that are in A, but might or might
not be in B, for publisher `publisher_code`. For those products, I need to
do something to the price. All of the fields, except for `product_price`,
are keys.

Here’s where I get to wondering: I’m concerned that the same product_id
might be used by more than one customer, so I want to filter on publisher. I
know that I can do this with a sub-select:

 UPDATE `A` LEFT JOIN
  (SELECT B.product_id FROM `C` JOIN B ON C.publisher_id = B.publisher_id
      JOIN `D` ON B.product_id = D.product_id
      WHERE C.publisher_code = 'Fred'
  ) AS `X`
  ON A.pub_product_id = X.pub_product_id
 SET D.product_price = 2 * D.product_price;

Is that the right / best way to handle this?

Regards,
 
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
 
860.674.8796 / FAX: 860.674.8341
 
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org