This discussion is archived
5 Replies Latest reply: Mar 18, 2013 9:03 AM by pollywog RSS

Question about a query.

tifil Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Help please !!!
  • 5. Re: Question about a query.
    pollywog Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points