Create a temporary table or run a 4 table Join with some more fields than others

Question

I have five tables, cis with the fields id, id_referencia, tipoci and numci, the others are cigeral, ciintercambio, cihoraextra and cicompensacao.

When I create one of the cis give a insert in the table referring to the type of ci and one insert on the table cis referencing the id of ci country maid id_referencia and in the field tipoci state the type and logic creates the number of the ci in the field numci, because although they are of different types all must follow the same order of numbering.

The problem is that I cannot create a query that searches all cis of all tables because the tables have some equal fields but some have more fields than others, for example:

cigeral owns the fields: id, tipoci, id_user, destino, assunto, dataci and discriminacao.

ciintercambio owns the fields: id, tipoci, id_user, destino, assunto, dataci, intercambista, dataintercambio and turno

Wanted a query return to me all fields of the 4 tables based on id_referÊncia table cis. fields that did not exist in the other tables were shown as null.

When I perform the query below it returns only the 3 cis exchange with their respective cis numbers, the other 8, which are of different types appear with all the data as null.

SELECT cis.numci, ciintercambio.* from cis LEFT JOIN ciintercambio on cis.id_referencia = ciintercambio.id AND cis.tipoci = 2

Answer 1

So I solved my problem. If anyone has any improvement to do in the consultation, I accept advice. Personal thanks.

CREATE view todas_as_cis AS
 SELECT cis.numci, u.nome, cis.id, i.tipoci, i.id_user, i.destino, 
 i.assunto, i.dataci, null as datacompensacao, null as discriminacao, 
 i.intercambista, i.dataintercambio,i.turno, null as turnoinicio, null 
 as turnofim, null as dataextra, null as credito, null as posto, null 
 as extrainicio, null as extrafim, null as agentes FROM `cis` left 
 join ciintercambio i on cis.id_referencia = i.id LEFT JOIN usuario u 
 ON i.id_user = u.id_user WHERE cis.tipoci = 2 
 UNION 
 SELECT cis.numci, u.nome, cis.id, g.tipoci, g.id_user, g.destino, 
 g.assunto, g.dataci, null as datacompensacao, g.discriminacao, null 
 as intercambista, null as dataintercambio, null as turno, null as 
 turnoinicio, null as turnofim, null as dataextra, null as credito, 
 null as posto, null as extrainicio, null as extrafim, null as agentes 
 FROM `cis` left join cigeral g on cis.id_referencia = g.id LEFT JOIN 
 usuario u ON g.id_user = u.id_user WHERE cis.tipoci = 1
 UNION
 SELECT cis.numci, u.nome, cis.id, c.tipoci, c.id_user, c.destino, 
 c.assunto, c.dataci, c.datacompensacao, null as discriminacao, null 
 as intercambista, null as dataintercambio, null as turno, null as 
 turnoinicio, null as turnofim, null as dataextra, null as credito, 
 null as posto, null as extrainicio, null as extrafim, null as agentes 
 FROM `cis` left join cicompensacao c on cis.id_referencia = c.id LEFT 
 JOIN usuario u ON c.id_user = u.id_user WHERE cis.tipoci = 4
 UNION
 SELECT cis.numci, u.nome, cis.id, h.tipoci, h.id_user, h.destino, 
 h.assunto, h.dataci, null as datacompensacao, null as discriminacao, 
 null as intercambista, null as dataintercambio, null as turno, 
 h.turnoinicio, h.turnofim, h.dataextra, h.credito, h.posto, 
 h.extrainicio, h.extrafim, h.agentes FROM `cis` left join cihoraextra 
 h on cis.id_referencia = h.id LEFT JOIN usuario u ON h.id_user = 
 u.id_user WHERE cis.tipoci = 3

Answer 2

Try this (it’s not even LEFT JOIN, it’s (INNER) JOIN):

SELECT cis.tipoci, i.id, cis.numci, i.id_user, i.destino, i.assunto, i.dataci,
       i.intercambista, i.dataintercambio, i.turno, NULL AS discriminacao
FROM cis
JOIN ciintercambio i ON i.id = cis.id_referencia AND cis.tipoci IN (2)

UNION

SELECT cis.tipoci, g.id, cis.numci, g.id_user, g.destino, g.assunto, g.dataci,
       NULL, NULL, NULL, g.discriminacao
FROM cis
JOIN cigeral g ON g.id = cis.id_referencia AND cis.tipoci IN (<TIPOS_DO_CIGERAL>)

UNION

...

Fill the ellipsis above with the other equivalent queries - for the case of each missing table, cihoraextra and cicompensacao -, always taking care that the columns that nay existing in the table are properly with your Nulls.

And name the columns in the first SELECT of the query.