Create a temporary table or perform a join of 4 tables one with more fields than others

Question

Create a temporary table or perform a join of 4 tables one with more fields than others

Navigation

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

1

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

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

The problem is that I can not create a query that fetches all cis from all tables because the tables have some equal fields but some have more fields than others, for example:

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

ciintercambio has the fields: id , tipoci , id_user , destino , assunto , dataci , intercambista , dataintercambio

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

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

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

mysql

asked by anonymous 02.06.2018 / 03:27

2 answers

0

So I solved my problem. If anyone has any improvement to make in the consultation, I take advice. thank you guys.

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

03.06.2018 / 20:09

Field with auto required in java script Receiving FCM messages in Java application

score 0 · Accepted Answer

Try like 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 in the ellipses above with the other equivalent queries - for each missing table, cihoraextra and cicompensacao -, always making sure that the columns strong> exist in the table are properly with their NULLs.

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