Bonjour, 

J'ai pour un devoir créé une table ADHERENT comportant une clé primaire n_adhérent et une clé étrangère parrainage dans la même table référençant cette clé primaire, en effet un adhérent peut être parrain d'un autre adhérent et être parrainé par un autre adhérent cette association est donc réflexive et représente une cardinalité parrainer 0,n et parrainé 0,1.

Cependant je n'arrive pas à formuler une requête correcte me permettant de récupérer pour un parrain X tous les membres qu'il a parrainé, cette question est formulé dans mon devoir tel quel : Obtenir la liste des adhérents ayant au minimum un parrain âgé de moins de 30 ans. 

Je n'est pas encore abordé les jointures de tables mais je pense qu'il faut creuser de ce coté là, mais je ne comprends pas trop le le fonctionnement de ce mécanisme, pouvez-vous s'il vous plait me donner un petit coup de main pour essayer de résoudre ce problème ?

CREATE TABLE ADHERENT(
n_adherent INTEGER,
email VARCHAR (100) NOT NULL ,
nom_adherent VARCHAR (100) NOT NULL ,
prenom_adherent VARCHAR (100) NOT NULL ,
date_naissance_adherent DATE NOT NULL,
ville_naissance_adherent VARCHAR(100) NOT NULL,
cp_naissance_adherent CHAR(5)NOT NULL,
ville_adherent VARCHAR (100) NOT NULL ,
cp_adherent CHAR (5) NOT NULL ,
adresse_adherent VARCHAR (150) NOT NULL ,
date_validation_inscription DATE NOT NULL ,
parrainage INTEGER,
PRIMARY KEY (n_adherent )
);


--CREATION CLEF ETRANGERE ADHERENT -> ADHERENT 

ALTER TABLE ADHERENT ADD CONSTRAINT frk_parrainage 
FOREIGN KEY (parrainage)  
REFERENCES ADHERENT (n_adherent);

-- TABLE ADHERENT 
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (1,'rene@sfr.fr','Rene','Jean',TO_DATE('01/02/1991','dd/mm/yyyy'),'Lyon','69001','Paris','75001','15 rue de la paix',TO_DATE('13/07/2014','dd/mm/yyyy'),NULL);
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (2,'sophie@orange.com','Sophie','Durand',TO_DATE('01/02/1989','dd/mm/yyyy'),'Lyon','69003','Paris','75005','45 rue leopold',TO_DATE('03/05/2016','dd/mm/yyyy'),1);
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (3,'thomas@free.fr','Thomas','Dupont',TO_DATE('01/02/1979','dd/mm/yyyy'),'Lyon','69003','Paris','75006','16 faubourg st josé',TO_DATE('02/04/2004','dd/mm/yyyy'),1);
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (4,'jose@free.fr','Jose','Louis',TO_DATE('01/02/1995','dd/mm/yyyy'),'Lyon','69004','Paris','75007','16 avenue st herbet',TO_DATE('18/06/2015','dd/mm/yyyy'),3);
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (5,'lea@free.fr','Lea','Bove',TO_DATE('01/02/1997','dd/mm/yyyy'),'Marseille','13000','Paris','75008','16 avenue st thomas',TO_DATE('11/02/2016','dd/mm/yyyy'),NULL);
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (6,'francis@free.fr','Francis','Duflanc',TO_DATE('01/02/1988','dd/mm/yyyy'),'Angoulême','16000','Poissy','78300','16 faubourg du chat',TO_DATE('02/05/2013','dd/mm/yyyy'),3);
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (7,'andre@wanadoo.fr','Andre','Delou',TO_DATE('01/02/1987','dd/mm/yyyy'),'Valence','26000','Paris','75017','16 boulevard du chat',TO_DATE('02/07/2014','dd/mm/yyyy'),4);
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (8,'michel@free.fr','Michel','Blanc',TO_DATE('01/09/1985','dd/mm/yyyy'),'Marseille','16001','Marseille','16003','16 faubourg de la sardine',TO_DATE('02/05/2016','dd/mm/yyyy'),1);
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (9,'arthur@orange.fr','Arthur','Kenek',TO_DATE('01/02/1993','dd/mm/yyyy'),'Bordeaux','33000','Maule','78580','16 faubourg du plateau',TO_DATE('05/05/2017','dd/mm/yyyy'),2);
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (10,'hernest@free.fr','Hernest','Henri',TO_DATE('01/02/1978','dd/mm/yyyy'),'Paris','75001','Lyon','69003','6 faubourg du roux',TO_DATE('02/05/2013','dd/mm/yyyy'),NULL);
INSERT INTO ADHERENT (n_adherent,email,nom_adherent,prenom_adherent,date_naissance_adherent,ville_naissance_adherent,cp_naissance_adherent,ville_adherent,cp_adherent,adresse_adherent,date_validation_inscription,parrainage)
VALUES (11,'elias@free.fr','Elias','Dutrouc',TO_DATE('01/02/1965','dd/mm/yyyy'),'Lyon','69004','Paris','75013','12 faubourg du chat',TO_DATE('02/05/2011','dd/mm/yyyy'),1);

Ci dessus vous pouvez consulter mon script de création de table, de clé étrangère et d'insertion de données, merci d'avance pour vos retours ! 

Bonjour,

Il s'agit bien d'utiliser les jointures, et même dans ce cas une auto-jointure (jointure sur la même table).

C'est un peu la base du SQL ... Et pour le SQL comme pour toute autre chose, il faut quand même se former un peu ... ne pas vouloir courir sans avoir appris à marcher ... mais passons ...

dester78 a écrit:

Obtenir la liste des adhérents ayant au minimum un parrain âgé de moins de 30 ans

L'idée c'est donc de faire une jointure entre la table ADHERENT et elle-même mais en utilisant une colonne différente :

SELECT A.*
FROM
	ADHERENT AS A
		INNER JOIN ADHERENT AS P
			ON A.parrainage = P.n_adherent
WHERE MONTHS_BETWEEN( P.date_naissance_adherent, SYSDATE )/12 < 30

On donne un alias différent à la table ADHERENT, un coup A (pour Adhérent) et un coup P (pour Parrain).

On sélectionne toutes les colonnes de la table A, liée à la table P selon la clé étrangère et la clé primaire.

Pour tout enregistrement dont le nombre de mois divisé par 12 (pour avoir des années) entre aujourd'hui et la date de naissance du parrain est inférieur à 30 ...

-
Edité par Benzouye 17 mars 2018 à 16:54:32

Benzouye, 

Merci pour tes conseils je viens d'essayer cette solution cependant la requête ne fonctionne pas, SQL Developer m'affiche comme message d'erreur "SQL command not properly ended". 

dester78 a écrit:

SQL Developer m'affiche comme message d'erreur "SQL command not properly ended". 

Etrange ... Es-tu sûr d'exécuter la même requête ? Pas de caractères en plus ou en moins ?

Au passage j'ai corrigé la condition MONTHS_BETWEEN j'avais inversé les dates ...

-
Edité par Benzouye 17 mars 2018 à 16:56:21

J'ai exécuté exactement la même requête cela devrait fonctionner pourtant les clés étrangères référencent bien les clés primaires dans la table ...

Cela vient seulement de la syntaxe SQL de la requête ...

Essaye de mettre un ; à la fin ...

SELECT A.*
FROM
    ADHERENT AS A
        INNER JOIN ADHERENT AS P
            ON A.parrainage = P.n_adherent
WHERE MONTHS_BETWEEN( P.date_naissance_adherent, SYSDATE )/12 < 30;

-
Edité par Benzouye 17 mars 2018 à 17:12:20

Yep je sais j'avais déjà mis le ; ^^  

Et du coup ?

Du coup ça ne fonctionnait pas, mon professeur m'a conseillé l'utilisation de IN pour récupérer les codes parrains de chaque adhérent. J'ai donc filtré les parrains en fonctions de leurs âges dans une sous requête et ensuite je les ai récupérer dans une autre requête : 

SELECT n_adherent,parrainage,nom_adherent,prenom_adherent FROM ADHERENT WHERE parrainage IN (

SELECT n_adherent FROM ADHERENT WHERE 30>=((SYSDATE - date_naissance_adherent)/365)

) ;

Merci Benzouye pour ton aide en tout cas ça m'a éclaircit sur les jointures. 

Et bien ... ça c'est du prof ...

A noter que ta requête ne fonctionneras pas si tu a plus de 1000 Adhérent de plus de 30 jours dans ta base de donnée (in est limité à 1000 lignes sur oracle il me semble).

Donc si tu peux ne retient pas la méthode qu'il ta donnée.

-
Edité par florent m 19 mars 2018 à 13:16:40

Le IN, la solution de facilité...

Certes ça fonctionne, mais c'est limité à la fois en terme de fonctionnalité (cf message de Florent M) et en terme de perf...

Ou comment donner de mauvaise habitude dès le début ...

A part le MONTH_BETWEEN, je vois pas en quoi la requête de Benzouye peut poser soucis :

SELECT DISTINCT A.*
FROM
    ADHERENT AS A
        INNER JOIN ADHERENT AS P
            ON A.parrainage = P.n_adherent
WHERE (SYSDATE - P.date_naissance_adherent)/365 < 30;

Parce que la, y'a 3 options pour faire la requête :

IN, EXISTS, auto-jointure.

Et la pire des 3, bah c'est le IN...

-
Edité par Tiffado 19 mars 2018 à 15:28:01

Tiffado a écrit:

WHERE (SYSDATE - P.date_naissance_adherent)/365 < 30;

Ce calcul n'est pas tout à fait juste Les années bissextiles ... D'où le MONTHS_BETWEEN d'Oracle qui est quand même pas mal pour calculer un age ...

Bonjour,

Sous Oracle, le fait d'"aliasser" une table en utilisant le terme (pourtant approprié) "AS" conduit au message d'erreur "la commande SQL ne se termine pas correctement".

La bonne requête est alors la suivante :

Merci Florent pour l'éclairage ... Je ne sais même pas pourquoi j'ai mis les "AS", je ne les utilises jamais ...

Salut tout le monde avec un peu de retard j'étais en pleine révision de partiels, merci encore pour les réponses !