>-----Original Message----- >From: shawn.gr...@sun.com [mailto:shawn.gr...@sun.com] >Sent: Saturday, April 11, 2009 3:50 PM >To: Jerry Schwartz >Cc: mysql@lists.mysql.com >Subject: Re: LEFT JOIN with third-table key > >Jerry Schwartz wrote: >> 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? >> > >I would have just added the product_id term to my ON clauses. They can >be every bit as complex as a WHERE clause and for LEFT JOINS to act like >LEFT JOINS, it's critical to get leave out of the WHERE clause any >non-null comparisons for values. Anytime you avoid a dependent subquery >your query should also be much faster. > >You can also use an INNER JOIN for all of these as you don't need to >update any non-matching rows in `A` with 2*NULL. > [JS] I understand that a subquery is a poor choice. Looking at my original post, I think I gave the wrong example for what I had in mind. You are right, in this case I could use INNER JOINs for everything.
I'll start another thread when I have a chance. Thanks. >-- >Shawn Green, MySQL Senior Support Engineer >Sun Microsystems, Inc. >Office: Blountville, TN > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
