Select with several Left Joins Sql Server

You can use the UNION clause that will "concatenate" the results of querys that have the same resulting column definitions. So you can set up a return of people with their proper types.

  

UNION

     

Combines the results of two or more queries into a single result set, which includes all rows belonging to all queries in the join. The UNION operation is different from using joins that combine columns from two tables.

     

The following are the basic rules for combining the result sets of two queries using UNION:

     

• The number and order of the columns must be the same in all queries.

  

• Data types must be compatible.

  

Below you will find the schema you have determined.

SELECT tpj.nome,
       'J' as tipo
  FROM tblPessoaJuridica tpj
  INNER JOIN tblPessoa tp ON tp.idPessoaJuridica = tpj.idPessoaFisica
UNION
SELECT tpf.nome,
       'F' as tipo
  FROM tblPessoaFisica tpf
  INNER JOIN tblPessoa tp ON tp.idPessoa = tpf.idPessoaFisica
UNION
SELECT tpp.nome,
       'P' as tipo
  FROM tblPessoaProdutor tpp
  INNER JOIN tblPessoa tp ON tp.idPessoa = tpp.idPessoaProdutor

You can use the above select to create a view that will make it easier to reuse.

  

VIEW

     

Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database.

Create an ID to identify the type of person and in programming use this id to select the information.

Example:

My Personal ID is 555 and my Personal ID is 2 (Individual):

if(IDTipoPessoa == 1){

}else if(IDTipoPessoa == 2){

}else if(IDTipoPessoa == 3){

}else{
   //retorna erro tipo inválido
}

Whenever you have a new type, just add an else. Or put the following, put everything in the same table, be it Product / Individual or Legal and select is like this:

     SELECT COALESCE(CNPJ,CPF,nomeProduto,'ERRO') as NOME FROM tblPessoa 
JOIN tblinformacoes on idPessoa = FKidPessoa

I think it could be so, it depends more on your business rule. Hugs,

Not considering restructuring your tables, LEFT JOIN is likely to be the best alternative after all.

In addition, you should only be worrying about performance in this case if you really have evidence that this is bottling your system. In this case, a possible solution would be to copy the data into another cache table with a denormalized structure to expedite the query, or else keep that table or parts of it in memory. However, these things are something you will only think of doing if you have a real need for it.

Another possibility is to make sure that all three tables have records for each ID of tblPessoa , even if filled with a bunch of nulls and thereby swap LEFT JOIN s with INNER JOIN s. However, if you are going to do this trick, it would be better to change the structure of the tables.

• First your tables are wrong
• If name is common to all, then it should be in the parent table
• Common fields are in parent table
• In the parent table you should define a type, which represents what type each person is
• Yes it can be slow because it maps wrong and does not create the correct indexes.
• Another way to do it is subquery instead of left jon