0
I have 3 tables, a flame custom_values with a field VALUE
of the kind TEXT
where it receives several types of information, soon I identify each value by the relationship with another table called custom_fields where I use two ID's
to find the VALUES
I need and a third table called issues which is related to the custom_values.
Example:
.---------------------------------. .------------------.
| CUSTOM_VALUES | | ISSUES |
|---------------------------------| |------------------|
| ID | CF_ID | ISSUE_ID | VALUE | | ID | START_DATE |
|---------------------------------| |------------------|
| 231 | 489 | 93 | 5 | | 93 | 2016-10-11 |
| 322 | 502 | 93 | 0.25 | | 94 | 2016-10-11 |
| 434 | 489 | 94 | 9 | | 95 | 2016-10-11 |
| 234 | 489 | 95 | 5 | | 96 | 2016-10-11 |
| 322 | 502 | 95 | 0.25 | | 97 | 2016-10-13 |
| 243 | 489 | 96 | 7 | | 98 | 2016-10-13 |
| 325 | 502 | 96 | 0.25 | | 99 | 2016-10-15 |
| 352 | 489 | 97 | 8 | | 100 | 2016-10-16 |
| 215 | 502 | 97 | 0.5 | | 101 | 2016-10-16 |
'---------------------------------' '------------------'
.------------------------------.
| CUSTOM_FIELDS |
|------------------------------|
| ID | DESCRICAO |
|------------------------------|
| 489 | Funcionários Treinados |
| 502 | Duração Treinamento |
'------------------------------'
I’m making a select
thus:
SELECT issues.id AS id,
CASE WHEN custom_fields.id = 489
THEN custom_values.value END AS funcionarios_treinados,
CASE WHEN custom_fields.id = 502
THEN custom_values.value END AS duracao_treinamento,
start_date
FROM issues
LEFT JOIN custom_values on issues.id = custom_values.issue_id
LEFT JOIN custom_fields on custom_fields.id = custom_values.cf_id
WHERE custom_values.value <> '' AND custom_fields.id IN (489, 502)
GROUP BY issues.id, custom_fields.id, custom_values.value, start_date
ORDER BY start_date
That returns that exit:
.----------------------------------------------------------------.
| id | funcionarios_treinados | duracao_treinamento | start_date |
|----|------------------------|---------------------|------------|
| 94 | 9 | | 2016-10-11 |
| 93 | 5 | | 2016-10-11 |
| 93 | | 0.25 | 2016-10-11 |
| 95 | 5 | | 2016-10-11 |
| 95 | | 0.25 | 2016-10-11 |
| 96 | 7 | | 2016-10-11 |
| 96 | | 0.25 | 2016-10-11 |
| 97 | 8 | | 2016-10-13 |
| 97 | | 0.5 | 2016-10-13 |
'----------------------------------------------------------------'
1 - How do I display only the values of funcionarios_treinados
and the duracao_treinamento
which are not null?
2 - How do I display the values in a row only, because when I compare with the CASE WHEN
the custom_field.id
he displays each custom_values.value
on each line, even if they remain the same issue_id
.
Perfect, and adding the clause
where f.funcionarios_treinados IS NOT NULL AND d.duracao IS NOT NULL
answers the two questions I asked! Thanks Camilo!!– Marcos Henzel