4 Replies Latest reply: Dec 8, 2012 10:49 AM by christian41936 RSS

    Suggested SQL query

    christian41936
      Hi

      I need you help for suggesting a query. These are the two tables :
      CREATE TABLE  "RENCONTRE" 
         (     "NUMPERS1" NUMBER, 
           "NUMPERS2" NUMBER, 
           "DATE_R" DATE, 
           "NUMLIEU" NUMBER, 
           "RESULTAT" VARCHAR2(20), 
            CONSTRAINT "RENCONTRE_PK" PRIMARY KEY ("NUMPERS1", "NUMPERS2", "DATE_R") ENABLE
         )
      /
      ALTER TABLE  "RENCONTRE" ADD CONSTRAINT "RENCONTRE_FK" FOREIGN KEY ("NUMLIEU")
             REFERENCES  "LIEU" ("NUMLIEU") ENABLE
      /
      
      CREATE TABLE  "PERSONNE" 
         (     "NUMPERS" NUMBER, 
           "NOM" VARCHAR2(50), 
           "PRENOM" VARCHAR2(50), 
           "ADRESSE" VARCHAR2(128), 
           "TELEPHONE" VARCHAR2(64), 
           "SEXE" VARCHAR2(20), 
            CONSTRAINT "PERSONNE_PK" PRIMARY KEY ("NUMPERS") ENABLE
         )
      /
      
      INSERT INTO PERSONNE (Numpers,Nom,Prenom,Adresse,Telephone,Sexe) values
      (1,'Delval','Bruno','Rue de la Fantaisie','06 52 45 89','masculin');
      INSERT INTO PERSONNE (Numpers,Nom,Prenom,Adresse,Telephone,Sexe) values (2,'Jolie','Emily','30 rue de arc en ciel','06 25 89 45','feminin');
      INSERT INTO PERSONNE (Numpers,Nom,Prenom,Adresse,Telephone,Sexe) values (4,'Dufour','Coralie','97 avenue du Four','05 78 46 89','feminin');
      INSERT INTO PERSONNE (Numpers,Nom,Prenom,Adresse,Telephone,Sexe) values (3,'Judoper','Marcel','23 rue de la resistance','06 89 45 78','masculin');
      
      INSERT INTO RENCONTRE (NUMPERS1,NUMPERS2,DATE_R,NUMLIEU,RESULTAT) VALUES (1,2,TO_DATE('01/20/2007', 'DD/MM/YYYY'),1, 'positif');
      INSERT INTO RENCONTRE (NUMPERS1,NUMPERS2,DATE_R,NUMLIEU,RESULTAT) VALUES (3,4,TO_DATE('01/15/2006', 'DD/MM/YYYY'),2,'negatif');
      INSERT INTO RENCONTRE (NUMPERS1,NUMPERS2,DATE_R,NUMLIEU,RESULTAT) VALUES (2,4,TO_DATE('01/01/0007', 'DD/MM/YYYY'),4,’positif’);
      The table RENCONTRE contains the NUMPERS of people who had met another person (NUMPERS1 and NUMPERS2 of the table RENCONTRE)
      NUMPERS1 and NUMPERS2 refers to NUMPERS of the table PERSONNE.

      Expected result of the query :

      Name of the people (column 'NOM' of table PERSONNE) who never had met a person of the same gender ('SEXE')

      -----------
      NOM
      -----------
      Judoper


      Thank you for your kind help.

      Christian
        • 1. Re: Suggested SQL query
          Hoek
          Thanks for providing a test case.
          However: did you test your script?
          The foreign key should not be created and there are not more than 12 months in a year ( , so TO_DATE('01/20/2007', 'DD/MM/YYYY') will not work)...
          • 2. Re: Suggested SQL query
            christian41936
            Thank you for you corrections
            INSERT INTO RENCONTRE (NUMPERS1,NUMPERS2,DATE_R,NUMLIEU,RESULTAT) VALUES (1,2,TO_DATE('20/01/2007', 'DD/MM/YYYY'),1, 'positif');
            INSERT INTO RENCONTRE (NUMPERS1,NUMPERS2,DATE_R,NUMLIEU,RESULTAT) VALUES (3,4,TO_DATE('15/01/2006', 'DD/MM/YYYY'),2,'negatif');
            INSERT INTO RENCONTRE (NUMPERS1,NUMPERS2,DATE_R,NUMLIEU,RESULTAT) VALUES (2,4,TO_DATE('01/01/0007', 'DD/MM/YYYY'),4,’positif’);
            These tables have been created under APEX. I copied the SQL from the SQL output of the Object browser. I think the foreign key was correct.

            I have tried this request but it did not gave the expected output:
            Select P1.nom
            from Personne P1, Rencontre R, Personne P2 
            where ((P1.numpers=R.numpers1 
                 and P2.numpers=R.numpers2) 
            or (P1.numpers=R.numpers2 
                 and P2.numpers=R.numpers1)) 
            and P1.sexe=P2.sexe;
            it gaves this output
            ---------
            NOM
            ---------
            Dufour
            Jolie

            instead of the expected ouput :
            ----------
            NOM
            ---------
            Judoper
            Delval

            Thank you for your suggestions

            Christian

            Edited by: Christian on Dec 7, 2012 4:08 PM
            • 3. Re: Suggested SQL query
              Hoek
              The foreign key might be correctly intended, but we don't have the table it refers to.
              But anyway, that's a side note, how about:
              SQL> select p.nom 
                2  from   personne p
                3  where  not exists ( select null
                4                      from   rencontre r
                5                      ,      personne p2
                6                      where  r.numpers1 = p.numpers
                7                      and    p2.numpers != p.numpers
                8                      and    p2.numpers = r.numpers2
                9                      and    p2.sexe = p.sexe
               10                    )
               11  and    not exists ( select null
               12                      from   rencontre r
               13                      ,      personne p2
               14                      where  r.numpers1 != p.numpers
               15                      and    p2.numpers = p.numpers
               16                      and    p2.numpers = r.numpers2
               17                      and    p2.sexe = p.sexe
               18                    );
              
              NOM
              --------------------------------------------------
              Delval
              Judoper
              
              2 rows selected.
              • 4. Re: Suggested SQL query
                christian41936
                Hello,

                It works fine thank you ! I could no imagine the statement so "complicated", it is not such complicated but I thought it was easier.

                Thank you very much for your kind help.

                Christian.