How to mount a Select to join columns

Question

How to mount a Select to join columns

Navigation

#1 by (1 votes)

0

I have a question that I do not even know if it is possible, it is as follows:

I would like to perform a select that joins up two columns.

Otherwise it would be:
Table Person with the fields cod_people - name - rg - razao_social - cnpj
Client table with field cod_client - cod_people
The concept of select would be: Find all clients displaying cod_client - name / social_reason - rg / cnpj

Example of recorded data: Table Person:

cod_pessoa  | Nome    | Rg            | Razao_Social       | Cnpj
1           | Luis    | 10.100.100-1  |                    |
2           | Carlos  | 20.200.200-2  |                    |
3           |         |               |Lanchonete do Paulo | 30.300.300/3000-30

Client table

cod_cliente  | cod_pessoa
1            | 1
2            | 2
3            | 3

I would like the result to be as follows:

cod_cliente  | Nome/Razao_social     |  Cpf/Cnpj
1            | Luis                  | 10.100.100-1
2            | Carlos                | 20.200.200-2
3            | Lanchonete do Paulo   | 30.300.300/3000-30

I'd like to get this result because I'm performing a project in C # and in one of the form's I do a customer search the result feeds a dataGrid. And the visual result of what I'm wanting would be nicer to a result with blank gaps.

I've tried in several ways, the one that came closest was this:

select cli.cod_cliente, pes.nome, pes.cpf from tab_Cliente cli
inner join tab_Pessoa pes on cli.cod_pessoa = pes.cod_pessoa
where pes.nome like '%%' --(condição)
union
select cli.cod_cliente, pes.razao_social, pes.cnpj from tab_Cliente cli
inner join tab_Pessoa pes on cli.cod_pessoa = pes.cod_pessoa
where pes.razao_social like '%%' --(condição)

Note: the condition will be the same on both select's

It joins the columns but it has some flaws like: it brings blank gaps when it gets empty in the condition or does not bring anything when in the condition it gets 'equal' to the bank's data.

I'm grateful for any suggestions right now.

- Update - Response - based on the response of dil_oliveira - Thank you very much

** Bank Used Sql Server

select 
  cli.cod_cliente, 
  (case when LEN(lTRIM(pes.nome)) > 0 then 
     Nome   
  else
     Razao_Social
  end) as 'Nome/Razao_social',
  (case when LEN(lTRIM(pes.rg)) > 0 then 
     Rg    
  else
     Cnpj
  end) as 'Cpf/Cnpj'
from tab_Pessoa pes
inner join tab_Cliente cli 
on pes.cod_pessoa = cli.cod_pessoa
where pes.nome like '%%' or pes.razao_social like '%%' order by 'Nome/Razao_social'

sql c# select

asked by anonymous 13.01.2015 / 00:51

1 answer

Error creating TabbarController at runtime My repeater does not render in browser

score 1 · Accepted Answer

You did not specify the database and this code works in MySQL, for other SGDBs look for corresponding LENGTH and TRIM functions;

select 
  cod_cliente, 
  (case when LENGTH(TRIM(Nome)) > 0 then 
     Nome   
  else
     Razao_Social
  end) as 'Nome/Razao_social',
  (case when LENGTH(TRIM(Rg)) > 0 then 
     Rg    
  else
     Cnpj
  end) as 'Cpf/Cnpj'
from Pessoa
inner join Cliente 
on Pessoa.cod_pessoa = Cliente.cod_pessoa

There is a more practical way to solve your problem, for example by creating a column in the Person table to define whether the record type is a person or company.