MySql select multiple columns from the same table with conditions

Question

MySql select multiple columns from the same table with conditions

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I need to select some data from a single table depending on the type of user, for example, an Individual user will have the RG and PIS field while a user will have the state Registration and Fancy name field.

I can execute a similar query with LEFT JOIN , however all fields are selected, differentiating only from having the field filled or null , according to the type of user. What I want is to select fields only when the type is matched, eg

Type 1 or 2: Select common fields;
Type = 1: Selects the Individual data (filled in or not);
Type = 2: Select the Corporate Data (filled in or not);

The query I currently have would look something 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 would like them not to appear, is it possible?

mysql sql

asked by anonymous 20.06.2017 / 21:07

2 answers

0

CASE, something like this

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

20.06.2017 / 21:17

TopMost Windows Forms Strange return when calling a function

score 2 · Accepted Answer

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

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

UNION 

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

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 corporate name for legal entities, and name of natural persons.

Column documento : would share cnpj data for legal entities, and cpf for individuals.

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

Then SQL gets clean, no problem whatsoever.

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

Edit:

Considering that it will load PF and PJ at different times, that is, they should not come in the same SELECT, it would do the programming.

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

...executa sql;

however, you may have trouble populating your variables with the name of the columns.