Mysql select multiple columns from the same table with conditions

Question

I need to select some data from a single table depending on the type of user, for example, a Personal user will have the field RG and PIS while a user will have the field State registration and fancy name.

I can run a query similar to LEFT JOIN, but all fields are selected, differing only from having the field filled or null, according to the type of user. What I want is to select the fields only when the type is corresponding, eg.:

• Type 1 or 2: Selects common fields;
• Type = 1: Selects Natural Person data (completed or not);
• Type = 2: Selects Legal Entity data (completed or not);

To query that I currently have would be more or less like this:

SELECT
    a.campo_a, a.campo_b, a.campo_c,

    -- Tipo 1
    b.campo_d, b.campo_e, --pode ter mais campos aqui

    -- Tipo 2
    c.campo_f, c.campo_g --pode ter mais campos aqui

FROM tabela AS a
    LEFT JOIN tabela AS b ON a.tipo = 1
    LEFT JOIN tabela AS c ON a.tipo = 2
WHERE a.id = :id

However, if I am selecting type 1, I still see the fields campo_f and campo_g, I wish they wouldn’t even show up, it’s possible?

Answer 1

1-Option: Union (only if same data type)

SELECT
    nome, 
    cpf, 
    rg
FROM pessoas where tipo = 1

UNION 

SELECT
    razao,
    cnpj,
    ie
FROM pessoas where tipo = 2;

Option: Coalesce (if fields are null when of another type)

SELECT
Coalesce(nome,razao) as nome,
Coalesce(cpf,cnpj) as documento,
Coalesce(rg,ie) as registro
FROM pessoas;

3-Option: Change the database (I would do so)

Column nome: would share data of social reason for legal entities, and name of natural persons.

Column documento: share cnpj data for legal entities, and Cpf for natural persons.

Column apelido: would share fancy name for legal persons, and nickname for natural persons.

Then the SQL gets clean, no problem at all.

Select
  nome,
  apelido,
  documento,
  ...
From pessoas;

Edit:

Whereas will load PF and PJ at different times, ie, should not come in the same SELECT, would by programming.

string sql;
if (pf)
 sql = "select nome, cpf, ... from pessoas;";
else
 sql = "select razao, cnpj, ... from pessoas";

...executa sql;

however, this way you may have problems filling your variables by column name.

Answer 2

CASE , something like that

select (case when tipo = 1 then cpf
             when tipo = 2 then cnpj
             else null end) cpf_cnpj, 
...