8 Replies Latest reply: Sep 11, 2014 4:28 AM by KayK RSS

    Recursive select

    Amparo

      Hello,

      I have the next tables:

      t1_group(id_group, description)

      t2_group_member(id_group_member, id_group, id_user) (the member can to be a group or a user)

      t3_user(id_user, name)

       

      ¿How can to get all id_user that they are in a group? i have proved with this select but id_user is null.

      SELECT id_grupo, id_usuario, id_subgrupo, LEVEL

      FROM gi_grupo_miembro

      where id_grupo=8

      CONNECT BY prior id_grupo = id_subgrupo

       

      What is the problem?

      Thank you. Regards,

      Amparo

        • 1. Re: Recursive select
          David Berger

          Hello Amparo

           

          Try this

          SELECT id_grupo, id_usuario, id_subgrupo, LEVEL

            FROM gi_grupo_miembro

          START WITH id_grupo = 8

          CONNECT BY prior id_grupo = id_subgrupo

          ;

           

          Regards, David

          • 2. Re: Recursive select
            Frank Kulash

            Hi, Amparo,

             

            Sorry, it's not clear what you're asking.

             

            Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
            Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
            Simplify the problem as much as possible.  Remove all tables and columns that play no role in this problem.  Just make sure the simplified problem still causes the problem.

            Always say which version of Oracle you're using (for example, 11.2.0.2.0).
            See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

            • 3. Re: Recursive select
              Amparo

              Hi,

               

              I give a example.

               

              Data in the table gi_grupo_miembro:

              id_grupo_miembro     id_grupo     id_subgrupo     id_user

              1                              8               5

              2                              8                                       10

              3                              5                                       11

              4                              5                                       12

              5                              5                                       10

               

              I want know the id_user of the id_grupo 8. Example

              10, 11,12,10

               

              With this sentence

              SELECT id_grupo, id_usuario, id_subgrupo, LEVEL

              FROM gi_grupo_miembro

              where id_grupo=8

              CONNECT BY prior id_grupo = id_subgrupo


              I get this data


              id_grupo     id_usuario      id_subgrupo     level

              8                                        5                        1

              8                                        5                         2

              8                                        5                         2

              8                                        5                         2

              8               10                                                1


              but I don't appear id_usuario (11, 12, 15) of id_subgrupo 5 and I need it.


              With this sentence

              SELECT id_grupo, id_usuario, id_subgrupo, LEVEL

                FROM gi_grupo_miembro

              START WITH id_grupo = 8

              CONNECT BY prior id_grupo = id_subgrupo


              I get this data


              id_grupo id_usuario id_subgrupo level

              8                    5          1

              8          21219                1


              and I need the all id_usuarios of id_grupo 8


              What is the problem?

              Thank you. Regards,


              • 4. Re: Recursive select
                Frank Kulash

                Hi,

                 

                It looks like you have PRIOR in the wrong place, and that you meant:

                 

                CONNECT BY  id_grupo  = PRIOR id_subgrupo

                 

                I can't actually test it until you post the CREATE TABLE and INSERT statements.

                • 5. Re: Recursive select
                  Amparo

                  I have it. It give me the result very well.

                   

                  Thank you very much.

                  Regards,

                  • 6. Re: Recursive select
                    Gaff

                    Please mark the question as "Answered" then.

                    • 7. Re: Recursive select
                      Amparo

                      Sure, but I can't find the options to mark it as "Answered".

                       

                      Do you know where is it?

                      • 8. Re: Recursive select
                        KayK

                        try the button correct answer