Relationship of tables

Question

Good Night, I have a database with several tables and need to make a query relationship 4 tables

CREATE TABLE IF NOT EXISTS `cad_cliente` (
      `id_cliente` smallint(5) unsigned NOT NULL,
      `nome_cliente` varchar(45) NOT NULL,
      `nome_dr` varchar(45) NOT NULL,
      `email_cliente` varchar(100) NOT NULL,
      `data_nascimento_cliente` date NOT NULL,
      `endereco_cliente` varchar(30) NOT NULL,
      `bairro_cliente` varchar(20) NOT NULL,
      `cep_cliente` varchar(20) NOT NULL,
      `cidade_cliente` varchar(45) NOT NULL,
      `estado_cliente` smallint(5) unsigned NOT NULL,
      `observacao` varchar(45) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   CREATE TABLE IF NOT EXISTS `cad_paciente` (
     `id_paciente` smallint(5) unsigned NOT NULL,
     `nome_paciente` varchar(45) NOT NULL,
     `id_cliente` varchar(45) NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

   CREATE TABLE IF NOT EXISTS `cad_trabalho` (
     `id_trabalho` smallint(5) unsigned NOT NULL,
     `id_cliente` varchar(45) NOT NULL,
     `id_paciente` varchar(45) NOT NULL,
     `id_dente` varchar(45) NOT NULL,
     `id_servico` varchar(45) NOT NULL,
     `id_cor` varchar(45) NOT NULL,
     `observacao_trabalho` varchar(45) NOT NULL,
     `data_entrada_trabalho` varchar(45) NOT NULL,
     `data_saida_trabalho` varchar(45) NOT NULL,
     `pronto_trabalho` varchar(45) NOT NULL,
     `valor_trabalho` varchar(45) NOT NULL,
     `pagamento_trabalho` varchar(45) NOT NULL,
     `foto1_trabalho` varchar(45) NOT NULL,
     `foto2_trabalho` varchar(45) NOT NULL,
     `foto3_trabalho` varchar(45) NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  CREATE TABLE IF NOT EXISTS `cad_estagio` (
  `id_estagio` smallint(5) unsigned NOT NULL,
  `id_trabalho` varchar(45) NOT NULL,
  `data_estagio` varchar(45) NOT NULL,
  `id_funcionario` varchar(45) NOT NULL,
  `tipo_estagio` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

now I want to relate these tables in a single query to display their data as follows

id_job, client name, patient name, date

and on the table cad_estagio may have several stages registered for the same id_trabalho how do I display this information in phpmyadmin I can with the following SELECT

SELECT *,DATE_FORMAT(data_saida_trabalho, '%d/%m/%Y') AS data_saida FROM cad_trabalho
JOIN cad_paciente ON cad_trabalho.id_paciente = cad_paciente.id_paciente
JOIN cad_cliente ON cad_paciente.id_cliente = cad_cliente.id_cliente
LEFT JOIN cad_estagio ON cad_trabalho.id_trabalho = cad_estagio.id_trabalho
WHERE cad_trabalho.pronto_trabalho = '0000-00-00' ORDER BY data_saida_trabalho ASC

Only when I display them on the page they come duplicated the same id_trabalho with all interns registered for that id_trabalho and does not display the id_trabalho for other results that have not staged registered, my php is as follows

 /* resultado da consulta */
$result = mysql_query("SELECT *,DATE_FORMAT(data_saida_trabalho, '%d/%m/%Y') AS data_saida FROM cad_trabalho
JOIN cad_paciente ON cad_trabalho.id_paciente = cad_paciente.id_paciente
JOIN cad_cliente ON cad_paciente.id_cliente = cad_cliente.id_cliente
LEFT JOIN cad_estagio ON cad_trabalho.id_trabalho = cad_estagio.id_trabalho
WHERE cad_trabalho.pronto_trabalho = '0000-00-00' ORDER BY data_saida_trabalho ASC");


        /* começa a construir a tabela no HTML */
        echo "<table><tr><th width='50'>COD</th><th width='250'>Cliente</th><th width='250'>Paciente</th><th width='100'>Data Entrega</th><th width='50'>Editar</th><th width='50'>Estagio</th></tr>";

        /* percorre o retorno da consulta */
        while($row = mysql_fetch_object($result)) {
            /* dentro do $row[] vai o nome da coluna da sua consulta */
            echo "<tr><td>$row->id_trabalho</td><td>$row->nome_cliente</td><td>$row->nome_paciente</td><td>$row->data_saida</td><td><a href=editar_trabalho.php?id=$row->id_trabalho>Editar</a></td><td><a href=editar_estagio.php?id=$row->id_trabalho>Estagio - $row->id_funcionario</a></td></tr>";
        }
        echo "</table>";

Answer 1

Opa I was able to solve with the following SELECT

SELECT *,DATE_FORMAT(data_saida_trabalho, '%d/%m/%Y') AS data_saida FROM cad_trabalho
JOIN cad_paciente ON cad_trabalho.id_paciente = cad_paciente.id_paciente
JOIN cad_cliente ON cad_paciente.id_cliente = cad_cliente.id_cliente
LEFT JOIN cad_estagio ON cad_trabalho.id_trabalho = cad_estagio.id_trabalho ORDER BY cad_estagio.id_estagio DESC
WHERE cad_trabalho.pronto_trabalho = '0000-00-00'
GROUP BY cad_trabalho.id_paciente
ORDER BY data_saida_trabalho ASC

But now how do I when I show the cad_estagio.id_estagio he shows me the last cad_trabalho.id_trabalho??

Answer 2

Follow the SQL tested by the database you gave me... From what I understand, it would be this.

SELECT * FROM cad_estagio AS cde 
LEFT JOIN cad_trabalho ON cad_trabalho.id_trabalho = cde.id_trabalho 
LEFT JOIN cad_paciente ON cad_trabalho.id_paciente = cad_paciente.id_paciente
LEFT JOIN cad_cliente ON cad_paciente.id_cliente = cad_cliente.id_cliente
WHERE cad_trabalho.pronto_trabalho = '0000-00-00'
ORDER BY cde.id_estagio DESC LIMIT 1

Answer 3

With the help of @Andrebaill we reached the following SELECT

SELECT *,cad_trabalho.id_trabalho as trabalho, DATE_FORMAT(data_saida_trabalho, '%d/%m/%Y') AS data_saida, DATE_FORMAT(cad_estagio.data_estagio, '%d/%m/%Y') AS data_estagio FROM cad_trabalho
JOIN cad_paciente ON cad_trabalho.id_paciente = cad_paciente.id_paciente
JOIN cad_cliente ON cad_paciente.id_cliente = cad_cliente.id_cliente
LEFT JOIN cad_estagio ON cad_trabalho.id_trabalho = cad_estagio.id_trabalho and cad_estagio.id_estagio = (select max(id_estagio) from cad_estagio where id_trabalho = cad_trabalho.id_trabalho)
WHERE cad_trabalho.pronto_trabalho = '0000-00-00'
GROUP BY cad_trabalho.id_paciente
ORDER BY cad_trabalho.data_saida_trabalho ASC

That solved my problem, thanks bro