MySQL joining with PHP

Question

Sometimes my eyes goes bonkers with these joins. Please help me build the select statement

product_version

 id    version
----------------
  1     apple
  2     orange
  3     pineapple

executions

 id    class       methods    plat_version    orig_prod_version
-----------------------------------------------------------------
  1    SomeTest     check          2               1 
  2    AnotTest     submit         3               2

I want to pull from the executions but convert the numbers from the version that is in the other table. I was trying to start off and just do one at this point. Here is what I have

SELECT e.id, 
e.class, 
e.plat_version, 
pv.id, 
pv.version, 
pv.version AS plat_version FROM executions e JOIN product_versions pv ON pv.version = e.plat_version

Thanks for the help.

UPDATE: I am hoping that it pulls the records from the executions table but instead of seeing numbers for plat_version and orig_prod_version, I want to see corresponding version fields from the other table

Answer 1

SELECT *
FROM executions e
LEFT JOIN product_version v
ON e.plat_version = v.id
LEFT JOIN product_version v2
ON e.orig_prod_version = v2.id

SQL JOIN Explanation

Answer 2

I'm thinking something like this:

SELECT 
e.id, 
e.class, 
pv_plat.version AS plat_version, 
pv_orig.version AS orig_prod_version,
FROM executions e 
JOIN product_versions pv_plat ON pv_plat.id= e.plat_version
JOIN product_versions pv_orig ON pv_orig.id= e.orig_prod_version

The idea is that you just join to the product_versions table twice, once for each id column that you have in the executions table.