How do I not display null values and display values on a line in this condition?

Question

How do I not display null values and display values on a line in this condition?

Navigation

#1 by (1 votes)

0

I have 3 tables, one is called custom_values with a VALUE field of type TEXT where it receives several types of information, so 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 that is related to custom_values . p>

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 doing a select like this:

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 this output:

.----------------------------------------------------------------.
| 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 duracao_treinamento that are not null?

2 - How do I display values on a line only, because when I compare with CASE WHEN custom_field.id it displays every custom_values.value on each line, even though they remain at issue_id .

postgresql

asked by anonymous 14.07.2017 / 16:25

1 answer

Error - Query: The subquery returned more than 1 value Continue running after accepting the Android Permission

score 1 · Accepted Answer

An alternative would be to use WITH for each type of records and then perform a query between them.

link

with funcionario_treinado as (
    select
        issues.id as id,
        custom_values.value funcionarios_treinados,
        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 = 489
group by
    issues.id,
    custom_fields.id,
    custom_values.value,
    start_date),
duracao_treinamento as (
    select
        issues.id as id,
        custom_values.value duracao,
        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 = 502
group by
    issues.id,
    custom_fields.id,
    custom_values.value,
    start_date
)
select
    f.id,
    f.funcionarios_treinados,
    d.duracao,
    f.start_date
from
    funcionario_treinado f
    left join duracao_treinamento d on (f.id = d.id)
where 
    f.funcionarios_treinados IS NOT NULL AND d.duracao IS NOT NULL
order by
    f.start_date;