3 Replies Latest reply on Mar 5, 2013 1:20 PM by 988510

    combine 2 select

    988510
      Oh man, I think I've lost my brain ... :-(

      I have 2 tables, one with software data per user and the other with data for the users. So how can I find out, what software a group of users has.

      Tab1: user, software
      Tab2: user, group

      What I want is something like that:

      select * from tab1
      where user=( select user from tab2 where group='Developers' )

      Thanks
      Peter
        • 1. Re: combine 2 select
          jeneesh
          user11096060 wrote:
          Oh man, I think I've lost my brain ... :-(

          I have 2 tables, one with software data per user and the other with data for the users. So how can I find out, what software a group of users has.

          Tab1: user, software
          Tab2: user, group

          What I want is something like that:

          select * from tab1
          where user=( select user from tab2 where group='Developers' )
          What you have written is a valid query.. Are you getting any error with this? Or Are you not getting the expected output?

          And, if you have multiple users for a group, you can use IN. One more thing, always use aliases with the subqueries..
          select * from tab1 t1
          where user IN ( select t2.user from tab2 t2 where t2.group='Developers' ) 
          And, do you actually have a column named USER? It is a reserved key word..
          • 2. Re: combine 2 select
            Hoek
            Oh man, I think I've lost my brain ... :-(
            Perhaps you left it at the coffee machine? ;)
            I have 2 tables, one with software data per user and the other with data for the users. So how can I find out, what software a group of users has.

            Tab1: user, software
            Tab2: user, group

            What I want is something like that:

            select * from tab1
            where user=( select user from tab2 where group='Developers' )
            It always helps to use a small testcase, are you perhaps looking for something like:
            SQL> create table t1
              2  as
              3  select 'A' username, 'Oracle' software from dual union 
              4  select 'B' username, 'Hoeksoft' software from dual union
              5  select 'C' username, 'Macrohard' software from dual;
            
            Table created.
            
            SQL> create table t2
              2  as
              3  select 'A' username, 'Developers' usergroup from dual union 
              4  select 'B' username, 'Developers' usergroup from dual union
              5  select 'C' username, 'Architects' usergroup from dual;
            
            Table created.
            
            SQL> select t1.* 
              2  from   t1
              3  ,      t2
              4  where  t1.username = t2.username
              5  and    t2.usergroup = 'Developers';
            
            U SOFTWARE
            - ---------
            A Oracle
            B Hoeksoft
            You should just join the tables, Oracle databases are born to join...
            • 3. Re: combine 2 select
              988510
              Thanks very much jeneesh, 'IN' was exactly what's been missing, now it works.