JOIN中重复的SQL结果 - Codeigniter

Question

I'm trying to return the list of 'clinicas' with yours 'telefones_clinicas' and 'planos_clinicas'. I could have more than one 'telefones_clinicas' and 'planos_clinicas' but it's not required.

But if I have more than one 'telefones_clincias' or 'planos_clinicas' my sql result is returning duplicated. How can I solve this?

Here is my tables:

Table: clinicas - Clomuns: id, autenticacao_email, autenticacao_id,cli_pag_id, descricao, foto, nome, qrcode, site, ativo, created, modified

Table: telefones_clinicas - Clomuns: id, tdc_dli_id, telefone, created, modified

Table: planos - Clomuns: id, plano

Table: planos_clinicas - Clomuns: clinicas_id, planos_id

I have more tables in this database, but I don't think they are the problem.

In this link you could see the duplicated returns: http://oakz.org/clinicas/clinicas/teste

I have only two 'clinicas'. The 'clinicas' of id 27 have two 'telefones' and four 'planos'. The 'clinicas' of id 28 have one 'telefones' and two 'planos'.

They are repeating each result of planos two times I think.

public function teste(){


    $this->db->trans_start();

    $this->db->select('clinicas.id AS id_clinica, nome, site, especialidade, descricao, foto, ativo, clinicas.created AS criacao_clinica, qrcode, cep, logradouro, complemento, numero, bairro, cidade, uf, telefone, planos.plano AS plano');
    $this->db->from('clinicas');
    $this->db->join('endereco_clinica', 'clinicas.id = endereco_clinica.ecs_cli_id');
    $this->db->join('Especialidades_clinicas', 'clinicas.id = Especialidades_clinicas.edc_cli_id');
    $this->db->join('especialidades', 'Especialidades_clinicas.edc_esp_id = especialidades.id');
    $this->db->join('planos_clinicas', 'clinicas.id = planos_clinicas.clinicas_id');
    $this->db->join('telefones_clinicas', 'clinicas.id = telefones_clinicas.tdc_cli_id');
    $this->db->join('planos', 'planos.id = planos_clinicas.planos_id');
    $this->db->order_by('id_clinica', 'ASC');
    $clinicas = $this->db->get();


            $xyz = 0;

            foreach($clinicas->result() as $row){
                $id_clinica = $row->id_clinica;
                $nome = $row->nome;
                $site = $row->site;
                $especialidade =$row->especialidade;
                $descricao = $row->descricao;
                $foto = $row->foto;
                $ativo = $row->ativo;
                $criacao_clinica = $row->criacao_clinica;
                $qrcode = $row->qrcode;
                $cep = $row->cep;
                $logradouro = $row->logradouro;
                $complemento = $row->complemento;
                $numero = $row->numero;
                $bairro = $row->bairro;
                $cidade = $row->cidade;
                $uf = $row->uf;
                $telefone = $row->telefone;
                $plano = $row->plano;
                $xyz++;
                echo 'tô na rodada '.$xyz;
                for($i = 0; $i <= count($plano); $i++) {
                    echo '<pre>',print_r($plano,1),'</pre>';
                    echo '----------------------------------------<br>';
                    echo '<pre>',print_r($row,1),'</pre>';
                    echo '----------------------------------------<br>';
                }
            }


    $this->db->trans_complete();

}

Answer 1

You can try using GROUP BY

 $this->db->group_by('clinicas.id');