5 Replies Latest reply: Mar 18, 2013 11:03 AM by pollywog RSS

    Question about a query.

    tifil
      Hello,

      I have a little problem with a query. Here you have it:
      SELECT  
           fc.tel_perso_ctf,
           a.adr_rue,
           a.adr_num,
           a.adr_cp,
           a.adr_ville,
           m.id_fam_rep1,
           m.situation_per,
           m.prenom_per,
           m.nom_per,
           m.email_perso_per,
           m.tel_portable_per,
           m.adr_num_per,
           m.adr_rue_per,
           m.adr_cp_per,
           m.adr_ville_per,
           m.profession_per,
           p.id_per,
              p.sexe_per,
              p.nom_per,
              p.prenom_per,
              p.date_naissance_per,
              il.code_lie,
              il.nom_lie,
           f.numero_alloc_fam,
              '#Begin../../....#' date_debut,
              '#End../../....#' date_fin,
              COUNT(DISTINCT date_rsv) nb_jours
      FROM    cr_inscription  i
          INNER JOIN
              cr_famille      f
              ON  i.id_fam = f.id_fam
          INNER JOIN
              cr_personne     p
              ON  m.id_per_ins = p.id_per
          INNER JOIN
              cr_inscription_lieu     il
              ON  i.id_ins = il.id_ins
          INNER JOIN
           cr_adresse     a
           ON f.id_adr = a.id_adr
          INNER JOIN
           cr_membre_famille     m
           ON f.id_fam = m.id_fam
          INNER JOIN
              cr_inscription_presence ip
              ON  i.id_ins = ip.id_ins
          INNER JOIN
           cr_famille_contact     fc
           ON f.id_fam = fc.id_fam
          
      WHERE   
              ip.date_rsv >= to_number(to_char(to_date('#Begin../../....#'),'YYYYMMDD'))
          AND ip.date_rsv <= to_number(to_char(to_date('#End../../....#'),'YYYYMMDD'))
          AND LOWER(il.code_lie) = lower('#Libellé court du lieu :#')
              
          AND ip.type_heure_rsv = 'P'
      GROUP BY
              
              fc.tel_perso_ctf,
           a.adr_rue,
           a.adr_num,
           a.adr_cp,
           a.adr_ville,
           m.id_fam_rep1,
           m.situation_per,
           m.prenom_per,
           m.nom_per,
           m.email_perso_per,
           m.tel_portable_per,
           m.adr_num_per,
           m.adr_rue_per,
           m.adr_cp_per,
           m.adr_ville_per,
           m.profession_per,
           p.id_per,
              p.sexe_per,
              p.nom_per,
              p.prenom_per,
              p.date_naissance_per,
              il.code_lie,
              il.nom_lie,
           f.numero_alloc_fam
      My query is good, but I have some duplicate, so I have to cross 2 tables, cr_membre_famille and cr_personne.
      But, this 2 tables are already in a INNER JOIN, so how can I do to cross this 2 tables ?
      Because I can not do this:

      Thanks a lot.

      Edited by: BluShadow on 18-Mar-2013 10:00
      added {noformat}
      {noformat} tags. Please read {message:id=9360002} and learn to do this yourself in future.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        • 1. Re: Question about a query.
          pollywog
          obviously I don't have your tables or data or expected results but maybe you could join those 2 tables right upfront using the with clause and then just reference that.
          something like
          WITH two_tables
               AS (SELECT whatever_you_nned
                     FROM    cr_membre_famille a
                          INNER JOIN
                             cr_personne b
                          USING (a.column_name = b.column_name)
                    ),
            SELECT fc.tel_perso_ctf,
                   a.adr_rue,
                   a.adr_num,
                   a.adr_cp,
                   a.adr_ville,
                   m.id_fam_rep1,
                   m.situation_per,
                   m.prenom_per,
                   m.nom_per,
                   m.email_perso_per,
                   m.tel_portable_per,
                   m.adr_num_per,
                   m.adr_rue_per,
                   m.adr_cp_per,
                   m.adr_ville_per,
                   m.profession_per,
                   p.id_per,
                   p.sexe_per,
                   p.nom_per,
                   p.prenom_per,
                   p.date_naissance_per,
                   il.code_lie,
                   il.nom_lie,
                   f.numero_alloc_fam,
                   date_debut,
                   date_fin,
                   COUNT (DISTINCT date_rsv) nb_jours
              FROM cr_inscription i
                   INNER JOIN cr_famille f
                      ON i.id_fam = f.id_fam
                   INNER JOIN /*cr_personne*/ two_tables p
                      ON   --m.id_per_ins = p.id_per and 
                      ON   whatever join makes sense here.
                   INNER JOIN cr_inscription_lieu il
                      ON i.id_ins = il.id_ins
                   INNER JOIN cr_adresse a
                      ON f.id_adr = a.id_adr
                   /*INNER JOIN cr_membre_famille m
                      ON f.id_fam = m.id_fam*/
                   INNER JOIN cr_inscription_presence ip
                      ON i.id_ins = ip.id_ins
                   INNER JOIN cr_famille_contact fc
                      ON f.id_fam = fc.id_fam
             WHERE     ip.date_rsv >=
                          TO_NUMBER (TO_CHAR (TO_DATE ('#Begin../../....#'), 'YYYYMMDD'))
                   AND ip.date_rsv <=
                          TO_NUMBER (TO_CHAR (TO_DATE ('#End../../....#'), 'YYYYMMDD'))
                   AND LOWER (il.code_lie) = LOWER ('#Libellé court du lieu :#')
                   AND ip.type_heure_rsv = 'P'
          GROUP BY fc.tel_perso_ctf,
                   a.adr_rue,
                   a.adr_num,
                   a.adr_cp,
                   a.adr_ville,
                   m.id_fam_rep1,
                   m.situation_per,
                   m.prenom_per,
                   m.nom_per,
                   m.email_perso_per,
                   m.tel_portable_per,
                   m.adr_num_per,
                   m.adr_rue_per,
                   m.adr_cp_per,
                   m.adr_ville_per,
                   m.profession_per,
                   p.id_per,
                   p.sexe_per,
                   p.nom_per,
                   p.prenom_per,
                   p.date_naissance_per,
                   il.code_lie,
                   il.nom_lie,
                   f.numero_alloc_fam
          • 2. Re: Question about a query.
            BEDE
            You may add join conditions in the where clause.
            Actually, in Oracle I don not use to write querries using "inner join", but I write the join conditions in the where clause as well

            Thus, I would write:


            select ...
            ,COUNT(DISTINCT date_rsv) nb_jours
            FROM cr_inscription i
            ,cr_famille f
            ,cr_personne p
            ,cr_inscription_lieu il
            ,cr_adresse     a
            ,cr_membre_famille     m
            ,cr_inscription_presence ip
            ,cr_famille_contact     fc
            WHERE
            ip.date_rsv >= to_number(to_char(to_date('#Begin../../....#'),'YYYYMMDD'))
            AND ip.date_rsv <= to_number(to_char(to_date('#End../../....#'),'YYYYMMDD'))
            AND LOWER(il.code_lie) = lower('#Libellé court du lieu :#')
            AND ip.type_heure_rsv = 'P'
            and i.id_fam = f.id_fam
            and m.id_per_ins = p.id_per
            and i.id_ins = il.id_ins
            and f.id_adr = a.id_adr
            and f.id_fam = m.id_fam
            and i.id_ins = ip.id_ins
            and f.id_fam = fc.id_fam
            ---- add here other join conditions if needed


            ---
            group by

            fc.tel_perso_ctf,
                 a.adr_rue,
                 a.adr_num,
                 a.adr_cp,
                 a.adr_ville,
                 m.id_fam_rep1,
                 m.situation_per,
                 m.prenom_per,
                 m.nom_per,
                 m.email_perso_per,
                 m.tel_portable_per,
                 m.adr_num_per,
                 m.adr_rue_per,
                 m.adr_cp_per,
                 m.adr_ville_per,
                 m.profession_per,
                 p.id_per,
            p.sexe_per,
            p.nom_per,
            p.prenom_per,
            p.date_naissance_per,
            il.code_lie,
            il.nom_lie,
                 f.numero_alloc_fam

            and you may add whatever filter or join conditions you need.
            I'm not sure what join conditions you need because I do not know what the foreign keys on your tables are (considering that tables are usually joined in querries by foreign-key columns)
            • 3. Re: Question about a query.
              tifil
              Thanks for your help :)

              In the first solution, with the with clauses, I didin't understant the "two-tables", what I put here ?

              In the second solution, when I do this:

              and i.id_fam = f.id_fam
              and m.id_per_ins = p.id_per
              and i.id_ins = il.id_ins
              and f.id_adr = a.id_adr
              and f.id_fam = m.id_fam
              and i.id_ins = ip.id_ins
              and f.id_fam = fc.id_fam

              It doesn't work. I have this error: Missing expression.
              • 4. Re: Question about a query.
                tifil
                Help please !!!
                • 5. Re: Question about a query.
                  pollywog
                  would it be possible to provide a small test case to illustrate your problem
                  with create table statements, and expected results,
                  it might be easier for people to work with and understand your requirements.