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

It would be easier for us to help you if you set up a SQL Fiddle — John Conde
– John Conde, Commented Jul 10, 2013 at 21:06
Have you tried this? What was the result? What exactly is your question? — user1864610
– user1864610, Commented Jul 10, 2013 at 21:06
Are you trying to get the version name for both version columns in the "executions" table at the same time? — bobwienholt
– bobwienholt, Commented Jul 10, 2013 at 21:08

Eric Petroelje · Accepted Answer · 2013-07-10 21:10:48Z

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.

answered Jul 10, 2013 at 21:10

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 10, 2013 at 21:10

Daniel W.

32.6k15 gold badges99 silver badges155 bronze badges

Collectives™ on Stack Overflow

MySQL joining with PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related