JOIN和INNER JOIN查询

Question

NB: I have asked a question just before (How to select from the db where version field is the recent one) and it's working well. It's not the duplicate.

SELECT p.* FROM `pricing` p 
JOIN (SELECT `distributor`,MAX(`version`) AS ver 
      FROM `pricing` GROUP BY `distributor`) mx 
  ON mx.ver=p.version AND p.distributor = mx.distributor

Using the above query I can select all the details from pricing table where distributor has the latest version.[Distributor will have different versions. I only need to select the latest version]. I have another table called product_picker. I only need to select the data from pricing table where code of pricing= code of product_picker. I have used the below code:

SELECT p.* FROM `pricing` p,pk.* 
FROM `product_picker` 
JOIN (SELECT `distributor`,MAX(`version`) AS ver 
      FROM `pricing` GROUP BY `distributor`) mx 
  ON mx.ver=p.version AND p.distributor = mx.distributor 
INNER JOIN `product_picker` pk ON p.code=pk.code

But I know it wouldn't work well. I am just learning the JOIN Clauses. How can I improve my code?

Thanks!

Answer 1

The mechanism is pretty simple:

SELECT p.*, pk.*
FROM 
    `pricing` p 
        JOIN (
            SELECT 
                `distributor`,
                MAX(`version`) AS ver 
            FROM `pricing` 
            GROUP BY `distributor`
        ) mx ON mx.ver = p.version AND p.distributor = mx.distributor
        JOIN `product_picker` pk ON pk.code = p.code -- the join has to be added after the other one, and the ON clause has to make it clear for SQL that the code is the criteria