Bring repeated records of 3 columns in the same table

Navigation
0

I have a table with transport data from my company employees: 

nome ! valor1 ! valor2 ! valor3

teste1 ! 6.50 ! 0.00 ! 0.00
teste2 ! 4.30 ! 2.80 ! 1.10
teste3 ! 8.40 ! 1.10 ! 0.00
teste4 ! 2.85 ! 1.10 ! 0.00

Where it is possible for the employee to pick up 3 bus lines to come to work, so I have these three columns.

The problem I am passing is the following, I want to return the data from the three columns without repetition, where I have all values of the bus lines of the employees.

I need to return, if possible, in a single column the data without repeating, without values '0.00' and sorted ascending:

result 

1.10
2.80
2.85
4.30 
6.50 
8.40
    
asked by anonymous 16.01.2018 / 19:43

2 answers

1

UNION : to join the 3 queries.

GROUP BY : to merge the same results. 

SELECT * FROM (
SELECT valor1 valor FROM TABELA
UNION
SELECT valor2 valor FROM TABELA
UNION
SELECT valor3 valor FROM TABELA) TABELAX
WHERE valor > 0
GROUP BY valor
ORDER BY valor

TABELAX : alias.

If the columns value1, value2 and value3 had only one value and the other 0.00, you could use CASE , doing everything in a single query.

Also create a VIEW of this select if in case always use.

    
16.01.2018 / 19:57
0

You can make one query per column, put it all together in a UNION query, sorting and grouping the result: 

SELECT * FROM (
    SELECT valor1 AS valor FROM tabela
    UNION ALL
    SELECT valor2 AS valor FROM tabela
    UNION ALL
    SELECT valor3 AS valor FROM tabela        
) AS resultado
WHERE valor > 0
GROUP BY valor
ORDER BY valor;

See running .

    
16.01.2018 / 19:57
How to import an existing project with angular-cli Redirect after submitting form with react-router-dom?