SQL & PL/SQL

Hi again...
I'll post the dsd once again to have an idea...

!http://img23.imageshack.us/img23/79/dsd.png!

All right... here's a sample data and table creation...

DROP TABLE PARTIE;
DROP TABLE RENCONTRE;
ALTER TABLE JOUEUR DROP CONSTRAINT FK_JOUEUR_EQUIPE;
DROP TABLE EQUIPE;
DROP TABLE JOUEUR;

CREATE TABLE Joueur(
    NoE INTEGER not null,
    NoJ INTEGER not null,
    NomJ VARCHAR2(80) NOT NULL,
    Adresse VARCHAR2(80) NOT NULL,
    Tel VARCHAR2(20),
    COJ VARCHAR2(9) NOT NULL,
    CONSTRAINT PK_JOUEUR PRIMARY KEY (NoJ, NoE),
);

CREATE TABLE EQUIPE(
    NoE INTEGER NOT NULL,
    NomE VARCHAR2(20) NOT NULL,
    NoJC INTEGER NOT NULL,
    CONSTRAINT PK_EQUIPE PRIMARY KEY (NoE),
    CONSTRAINT FK_EQUIPE_JOUEUR FOREIGN KEY (NoJC,NoE) REFERENCES JOUEUR(NoJ,NoE)
);

ALTER TABLE JOUEUR
    ADD CONSTRAINT FK_JOUEUR_EQUIPE FOREIGN KEY (NoE) REFERENCES EQUIPE(NoE);

CREATE TABLE RENCONTRE(
    NoR INTEGER NOT NULL,
    NoEH INTEGER NOT NULL,
    NOEV INTEGER NOT NULL,
    CONSTRAINT PK_RENCONTRE PRIMARY KEY (NoR),
    CONSTRAINT FK_RENCONTRE_EQUIPE FOREIGN KEY (NoEH) REFERENCES EQUIPE(NoE),
    CONSTRAINT FK_RENCONTRE_EQUIPE2 FOREIGN KEY (NoEV) REFERENCES EQUIPE(NoE)
);

CREATE TABLE PARTIE(
    NoR INTEGER NOT NULL,
    NoP INTEGER NOT NULL,
    NoJH INTEGER NOT NULL,
    NoJV INTEGER NOT NULL, 
    EG VARCHAR2(1) NOT NULL,
    CONSTRAINT PK_PARTIE PRIMARY KEY (NoR, NoP),
    CONSTRAINT FK_PARTIE_RENCONTRE FOREIGN KEY (NoR) REFERENCES RENCONTRE(NoR),
    CONSTRAINT CHK_PARTIE_EG CHECK (EG IN ('H', 'V'))
);

ALTER TABLE JOUEUR 
    DISABLE CONSTRAINT FK_JOUEUR_EQUIPE;

ALTER TABLE EQUIPE 
    DISABLE CONSTRAINT FK_EQUIPE_JOUEUR;

ALTER TABLE RENCONTRE
    DISABLE CONSTRAINT FK_RENCONTRE_EQUIPE;

ALTER TABLE RENCONTRE
    DISABLE CONSTRAINT FK_RENCONTRE_EQUIPE2;

ALTER TABLE PARTIE
    DISABLE CONSTRAINT FK_PARTIE_RENCONTRE;


INSERT INTO JOUEUR VALUES(1,2,'Mirrione, Bastien','851 Rue Laurendeau','514-390-0579','X11944728');
INSERT INTO JOUEUR VALUES(1,9,'Saunier, Alejandro','3864 Avenue de Monkland','514-286-8965','X10556464');
INSERT INTO JOUEUR VALUES(1,10,'Chelihi, Bahda-Delattre','3611 Rue Jean-Talon Est','514-286-5629','X11959597');
INSERT INTO JOUEUR VALUES(1,13,'Phan, Nady','5083 Boulevard Maurice-Duplessis','514-351-5234','X10603182');
INSERT INTO JOUEUR VALUES(1,17,'Michetti, Aziz','6440 Boulevard Henri-Bourassa Ouest','514-286-1212','X11450898');


INSERT INTO EQUIPE VALUES(1,'Paramount',2);
INSERT INTO EQUIPE VALUES(2,'Acme',20);
INSERT INTO EQUIPE VALUES(5,'Brillants',13);
INSERT INTO EQUIPE VALUES(8,'Xtreme',17);
INSERT INTO EQUIPE VALUES(10,'Apex',12);
INSERT INTO EQUIPE VALUES(13,'Maximus',1);

INSERT INTO RENCONTRE VALUES(1,2,1);
INSERT INTO RENCONTRE VALUES(2,5,10);
INSERT INTO RENCONTRE VALUES(3,13,1);
INSERT INTO RENCONTRE VALUES(4,10,5);
INSERT INTO RENCONTRE VALUES(5,10,2);
INSERT INTO RENCONTRE VALUES(6,5,10);


INSERT INTO PARTIE VALUES(1,1,16,17,'V');
INSERT INTO PARTIE VALUES(1,2,20,9,'H');
INSERT INTO PARTIE VALUES(1,3,11,20,'H');
INSERT INTO PARTIE VALUES(1,4,15,10,'H');
INSERT INTO PARTIE VALUES(2,1,14,15,'H');
INSERT INTO PARTIE VALUES(2,2,18,16,'H');
INSERT INTO PARTIE VALUES(2,3,14,19,'H');
INSERT INTO PARTIE VALUES(3,1,15,2,'H');
INSERT INTO PARTIE VALUES(3,2,15,9,'V');
INSERT INTO PARTIE VALUES(3,3,1,20,'H');
INSERT INTO PARTIE VALUES(3,4,1,2,'V');
INSERT INTO PARTIE VALUES(3,5,15,17,'V');
INSERT INTO PARTIE VALUES(4,1,8,13,'H');
INSERT INTO PARTIE VALUES(4,2,14,16,'V');
INSERT INTO PARTIE VALUES(4,3,6,13,'H');
INSERT INTO PARTIE VALUES(4,4,13,11,'V');
INSERT INTO PARTIE VALUES(4,5,8,16,'H');
INSERT INTO PARTIE VALUES(5,1,5,11,'V');
INSERT INTO PARTIE VALUES(5,2,19,20,'V');
INSERT INTO PARTIE VALUES(5,3,16,14,'V');
INSERT INTO PARTIE VALUES(6,1,11,3,'H');
INSERT INTO PARTIE VALUES(6,2,16,13,'V');
INSERT INTO PARTIE VALUES(6,3,13,14,'H');
INSERT INTO PARTIE VALUES(6,4,16,13,'H');

ALTER TABLE JOUEUR 
    ENABLE CONSTRAINT FK_JOUEUR_EQUIPE;

ALTER TABLE EQUIPE 
    ENABLE CONSTRAINT FK_EQUIPE_JOUEUR;

ALTER TABLE RENCONTRE
    ENABLE CONSTRAINT FK_RENCONTRE_EQUIPE;

ALTER TABLE RENCONTRE
    ENABLE CONSTRAINT FK_RENCONTRE_EQUIPE2;

ALTER TABLE PARTIE
    ENABLE CONSTRAINT FK_PARTIE_RENCONTRE;

All right!
If you copy/paste this you should be able to run it without errors.

Now, my problem...

I have to make a table listing the teams (equipe), the number of "rencontre" the attended (there are from 3 to 5 matches(partie) per meeting(rencontre))
as well as the number of victories (EG)..

but the victory is listed only by "V" for visiting or "H" for host under the Partie table, column EG

and...
my big, big problem, is trying to connect the Equipe table to Rencontre
PK of Equipe is NoE...
FK in Rencontre is
NoEH for host team
and NoEV for visiting team...


This is my attempt... not to solve the problem, but only to try to connect the tables with a very simple request

select r1.NOEH, r2.NOEV, p.NOR , p.NOP, p.EG
from partie p, rencontre r1, equipe e, rencontre r2
where p.NOR=r.NOR NOR
and e.noe=r1.NOEH
and e.noe=r2.NOEV
group by r1.NOEH, r2.NOEV, p.NOR , p.NOP, p.EG;

Big problem here... thanks for your time people