SQL: Compare between 2 columns and return only 1

Asked

Viewed 112 times

0

I have a table of clients that includes clients PF and PJ. In this table, if the client has CPF has no CNPJ, if CNPJ has no CPF. I need a query that checks which of the two columns is not null and returns only it. Type

SELECT nome, (cnpj or cpf) as documento FROM CLIENTES
where ...

My database is Mysql, but if you have the code in another relational BD I accept, so I can search about.

mysql sql select

2 answers

2


You can use the function COALESCE, either returns the first value of a list that is not null:

SELECT nome, COALESCE(cpf, cnpj) AS documento

Another option would be using CASE WHEN to solve this:

SELECT nome,
CASE 
    WHEN cpf IS NOT NULL
    THEN cpf
    ELSE cnpj
END
) AS documento

Documentation: COALESCE and CASE WHEN

-2

You have a modeling problem in your bank. A multi-valued attribute should originate a third table (own table), referenced by foreign keys. In this case, your clients table should be linked to other two, PF and PJ. It is the classic example of the entity person who should be specialized in its sub-classes: natural person and legal person.

  • Thanks friend for the answer, but I have 2 tables (PF and PJ), but the query is only one. I wanted to simplify the question so that it becomes easier and objective to be answered.

Browser other questions tagged mysql sql select

You are not signed in. Login or sign up in order to post.