CODEWITHSUNDEEP
mysql
SIMDev
SIMDev

Reputation: 29

MySQL one result from 2 tables

Hi all,

I've the following tables:

jobs_active:

| id | date_id    | job_id | result   |
|-------------------------------------|
| 1  | 2017-08-28 | 1      | failed   |
|-------------------------------------|
| 2  | 2017-08-28 | 2      | failed   |
|_____________________________________|

jobs_history:

| id | job_id | date_id    | job_id | result   |
|----------------------------------------------|
| 1  | 1      | 2017-08-27 | 1      | failed   |
|----------------------------------------------|
| 2  | 1      | 2017-08-26 | 1      | success  |
|----------------------------------------------|
| 3  | 2      | 2017-08-27 | 2      | failed   |
|----------------------------------------------|
| 4  | 2      | 2017-08-26 | 2      | failed   |
|______________________________________________|

And I want to get this result:

                             (2017-08-28)| (2017-08-27)    | (2017-08-26)


| id | date_id    | job_id | result_now  | result_lastDay1 | result_lastDay2 |
|----------------------------------------------------------------------------|
| 1  | 2017-08-27 | 1      | failed      | failed          | success         |
|----------------------------------------------------------------------------|
| 2  | 2017-08-26 | 2      | failed      | failed          | failed          |
|____________________________________________________________________________|

The column "result_lastDayN" should be dynamic. So that I can select like 10 last days if needed.

I tried this already with join, and union, but I didn't get it to work. Does anyone have an idea if this is possible?

Upvotes: 1

Views: 38

Answers (1)

Mariano L
Mariano L

Reputation: 1879

have you tried with subqueries?

select ja.*,
(select result from jobs_history jh where job_id = ja.id and jh.date = j.date - INTERVAL 1 DAY) result_lastDay1,
(select result from jobs_history jh where job_id = ja.id and jh.date = j.date - INTERVAL 2 DAY) result_lastDay2
 from 
jobs_active ja

Upvotes: 1

Related Questions