LEFT JOIN chien AS  cp ON ce.lof_pere = cp.lof
LEFT JOIN chien AS  cm ON ce.lof_mere = cm.lof

-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Mer 05 Octobre 2011 à 15:36
-- Version du serveur: 5.5.8
-- Version de PHP: 5.3.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Base de données: `malivote`
--

-- --------------------------------------------------------
USE malivote;
--
-- Structure de la table `chien`
--

CREATE TABLE IF NOT EXISTS `chien` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lof_pere` int(20) NULL,
  `lof_mere` int(20) NULL,
  `race` varchar(255) NOT NULL,
  `nom` varchar(32) NOT NULL,
  `affixe` varchar(40) NOT NULL,
  `sex` varchar(11) NOT NULL,
  `lof` varchar(32) NOT NULL,
  `puce` varchar(50) NOT NULL,
  `photo` varchar(50) NOT NULL,
  `nee` varchar(32) NOT NULL,
  `taille` varchar(32) NOT NULL,
  `poids` varchar(32) NOT NULL,
  `description` text NOT NULL,
  `d_inscription` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Contenu de la table `chien`
--

INSERT INTO `chien` (`id`, `lof_pere`, `lof_mere`, `race`, `nom`, `affixe`, `sex`, `lof`, `puce`, `photo`, `nee`, `taille`, `poids`, `description`, `d_inscription`) VALUES
(1, NULL, NULL, 'Berger belge Malinois', 'Vatan', 'de la Cite des Foucault', '(M)', '00254855-00254', '000022558885122', '<Img src="images/vatan.PNG">', '12/12/2006', '63 cm', '33 Kg', 'Erat autem diritatis eius hoc quoque indicium nec obscurum nec latens, quod ludicris cruentis delectabatur et in circo sex vel septem aliquotiens vetitis certaminibus pugilum vicissim se concidentium perfusorumque sanguine specie ut lucratus ingentia laetabatur.', 0),
(2, NULL, NULL, 'Berger belge Malinois', 'vaBimbo', 'des deux sabres', '(F)', '2', '', '<Img src="images/bimbo.PNG">', '', '', '', '', 0),
(3, 1, 2, 'Berger belge Malinois', 'vaTop Gun', 'des Plaines de Thierache', '(M)', '1', '', '', '', '', '', '', 0);

mysql> SET @p_id_chien = 3;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT
    ->   ce.nom      AS nom_chien,
    ->   ce.affixe   AS affixe_chien,
    ->   cp.nom      AS nom_pere,
    ->   cp.affixe   AS affixe_pere,
    ->   cm.nom      AS nom_mere,
    ->   cm.affixe   AS affixe_mere
    -> FROM chien AS         ce
    ->   LEFT JOIN chien AS  cp ON ce.lof_pere = cp.id
    ->   LEFT JOIN chien AS  cm ON ce.lof_mere = cm.id
    -> WHERE ce.id = @p_id_chien;
+-----------+--------------------------+----------+-------------------------+----------+-----------------+
| nom_chien | affixe_chien             | nom_pere | affixe_pere             | nom_mere | affixe_mere     |
+-----------+--------------------------+----------+-------------------------+----------+-----------------+
| vaTop Gun | des Plaines de Thierache | Vatan    | de la Cite des Foucault | vaBimbo  | des deux sabres |
+-----------+--------------------------+----------+-------------------------+----------+-----------------+
1 row in set (0.00 sec)

mysql> SET @p_id_chien = 2;
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT
    ->   ce.nom      AS nom_chien,
    ->   ce.affixe   AS affixe_chien,
    ->   cp.nom      AS nom_pere,
    ->   cp.affixe   AS affixe_pere,
    ->   cm.nom      AS nom_mere,
    ->   cm.affixe   AS affixe_mere
    -> FROM chien AS         ce
    ->   LEFT JOIN chien AS  cp ON ce.lof_pere = cp.id
    ->   LEFT JOIN chien AS  cm ON ce.lof_mere = cm.id
    -> WHERE ce.id = @p_id_chien;
+-----------+-----------------+----------+-------------+----------+-------------+
| nom_chien | affixe_chien    | nom_pere | affixe_pere | nom_mere | affixe_mere |
+-----------+-----------------+----------+-------------+----------+-------------+
| vaBimbo   | des deux sabres | NULL     | NULL        | NULL     | NULL        |
+-----------+-----------------+----------+-------------+----------+-------------+
1 row in set (0.01 sec)

mysql>  -- On crée une variable pour définir une valeur identifiant un parent;
mysql> SET @p_id_parent = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> -- On exécute la requête qui prendra en compte la variable précédente;
mysql> SELECT
    ->   ce.id       AS id_parent,
    ->   ce.nom      AS nom_parent,
    ->   ce.affixe   AS affixe_parent,
    ->   ce.lof_pere AS id_pere_parent,
    ->   cp.id       AS id_enfant,
    ->   cp.nom      AS nom_enfant,
    ->   cp.affixe   AS affixe_enfant,
    ->   cp.lof_pere AS id_pere_enfant
    -> FROM chien AS         ce
    ->   LEFT JOIN chien AS  cp ON ce.lof_pere = cp.id
    -> WHERE cp.id = @p_id_parent;

+-----------+------------+-------------------------+----------------+-----------+------------+-------------------------+----------------+
| id_parent | nom_parent | affixe_parent           | id_pere_parent | id_enfant | nom_enfant | affixe_enfant           | id_pere_enfant |
+-----------+------------+-------------------------+----------------+-----------+------------+-------------------------+----------------+
|         1 | Vatan      | de la Cite des Foucault |              1 |         1 | Vatan      | de la Cite des Foucault |              1 |
+-----------+------------+-------------------------+----------------+-----------+------------+-------------------------+----------------+
1 row in set (0.02 sec)