Combine several tables and bring two records of a specific table mysql

1

Greetings to the community, I do not usually ask much, but this time something is giving me a headache, I am a beginner and I have a problem, I do not intend to solve it but if they guide me it is a great help.

Veran I have two tables cases_case and cases_seguys , in the first I keep basic data of the cases as the date of creation a subject id and what is exposed, in the second table I always keep 1 first register and in one of the fields called origin I place you, creation of the case, and in addition to open status, also the id of the user who created it and the id to the user who was assigned if he can solve the case, also comments that for example : the client called etc. etc., now this is always this record and also when they close the case the esatus is changed to closed and also I always keep the assigned user and the id of the person who closed it.

My problem is when it comes to making the reports, I have an ugly code:

SELECT casos_caso.id, casos_caso.nivel, casos_caso.creado, clientes.identificacion, clientes.codigo_cliente, clientes.razonsocial, clientes.zona, asuntos_asunto.nombre as asunto, asuntos_asunto.id as idAsunto, estados.estado, municipios.municipio, casos_seguimiento.grupo_escalamiento, casos_seguimiento.cargo_se_asigno_caso, casos_seguimiento.id as idSeguimiento, casos_seguimiento.estatus, casos_seguimiento.origen, casos_seguimiento.creado as fechaSeguimiento, escalamientos.nombre, usuarios.usuario from casos_caso  JOIN clientes on casos_caso.id_cliente = clientes.id  JOIN asuntos_asunto on casos_caso.id_asunto = asuntos_asunto.id  JOIN estados on clientes.estado = estados.id JOIN municipios on clientes.municipio = municipios.id_municipio join casos_seguimiento on casos_caso.id = casos_seguimiento.id_caso JOIN escalamientos on casos_seguimiento.grupo_escalamiento = escalamientos.id JOIN usuarios on casos_seguimiento.id_usuario = usuarios.id

(sorry but I was a bit desperate and I'm really going from beginner to mysql and I have some knowledge about the join) that brings me all the separate records in each line if there are 5 comments this brings them all but I would only like to bring only the one that says the creation of the case and the one that says closed, I currently have 2 cases, one with 5 comments and another with just the creation of the case, it is possible some idea or direction where only 2 lines come out and once bring the id, the case, creation of the case, both date and the two users who created it and the one who closed it, I leave a table more or less as it should be

<table>
  <tr>
  <td class="text-left">Caso #</td>
                    <td class="text-left">Fecha Caso.</td>
                    <td class="text-left">Fecha Cierre.</td>

                    <td class="text-left">Razón Social/Nombre.</td>
                    <td class="text-left">Código.</td>
                    <td class="text-left">Rif/Cédula.</td>


                    <td class="text-left">Operador Creo el caso.</td>

                    <td class="text-left">Usuario Asignado.</td>
                    <td class="text-left">Usuario Cerró el caso.</td>
                    <td class="text-left">Estatus.</td>
  <tr>
</table>

Many thank you very much for the help you can give me. even improve the bd, or the code, really thank you very much

    
asked by leoncenteno 30.01.2018 в 19:02
source

0 answers