I am new in MYSQL and I looking how can a JOIN two tables and bring a result with some conditions:
SERVICE TABLE
| cod | Company | client | cod_prod |
|---|---|---|---|
| 9221 | A | 3936 | 10 |
| 9221 | A | 3936 | 55 |
| 9221 | A | 3936 | 3 |
| 9221 | A | 3936 | 18 |
| 9222 | B | 105 | 1 |
| 9222 | B | 105 | 18 |
| 9224 | A | 200 | 6 |
| 9224 | A | 200 | 66 |
| 9224 | A | 200 | 12 |
| 9224 | B | 146 | 18 |
| 9224 | B | 146 | 60 |
LOG TABLE
| cod | Company | Status | date |
|---|---|---|---|
| 9221 | A | entering | 05/11/2022 |
| 9221 | A | redo | 06/11/2022 |
| 9221 | A | working | 07/11/2022 |
| 9222 | B | new | 09/11/2022 |
| 9222 | B | diagnosis | 10/11/2022 |
| 9224 | A | working | 11/11/2022 |
| 9224 | A | done | 12/11/2022 |
| 9224 | B | new | 05/11/2022 |
| 9224 | B | diagnosis | 06/11/2022 |
I saw many examples, but none of them helped me to build these queries The 'cod' sometimes is equal, but each Company has a unique 'cod'. I trying to find two summaries for each company with the last date but only when status is 'diagnosis' and 'done'
SUMMARY COMPANY A
| cod | Company | client | Last Status | Last date |
|---|---|---|---|---|
| 9224 | A | 200 | done | 12/11/2022 |
SUMMARY COMPANY B
| cod | Company | client | Last Status | Last date |
|---|---|---|---|---|
| 9222 | B | 105 | diagnosis | 10/11/2022 |
| 9224 | B | 146 | diagnosis | 06/11/2022 |
I'm trying this https://dbfiddle.uk/zKjLkjBx
CodePudding user response：
With the schema you provided it is impossible to get the results you are seeking - there is no way to link the client to the log table.