[mysql][php] Problematic join results from two tables and filtering in php

debugcn Published at Dev

ano bityu

I am looking for a way to get data about the production process. I have two tables:

production: stores product information

timeline: there are details of the employee and when he made the production step

production:

--------------------------
internal | type of closing
--------------------------
123      | close
122      | open

timeline:
------------------------------------------------
id | internal | employee | date        | step
------------------------------------------------
1  | 123      | E01      | 2017-11-11  | diag
2  | 123      | E03      | 2017-11-12  | rep
3  | 122      | E06      | 2017-11-05  | diag

Steps from the column steps are predetermined (diag,rep,test)

I would like to get a result:

----------------------------------------------------------------------------------------
internal | diag | date_diag  | rep  | date_rep   | test  | date_test  | type of closing
----------------------------------------------------------------------------------------
123      | E01  | 2017-11-11 | E03  | 2017-11-12 | NULL  | NULL       | close
122      | E06  | 2017-11-05 | NULL | NULL       | NULL  | NULL       | open

To filter this result later by date_rep(or)date_test(or)date_diag and type of closing (close/open). How can this be done using Mysql and PHP? With the best performance, because the base is huge.

kiks73

You should use different aliases on the same table, joining the table in a way like this:

SELECT T1.internal, 
       T1.employee, 
       T1.date as date_diag,
       T2.employee as rep,
       T2.date as date_rep,
       NULL,
       NULL,
       P.description
FROM timeline T1
INNER JOIN production P ON T1.internal = P.internal 
LEFT JOIN timeline T2 ON T1.internal = T1.internal and T2.step = 'rep'
WHERE T1.step ='diag'

This is a starting point close enough to the solution.

Collected from the Internet

Please contact [email protected] to delete if infringement.