I am trying to create an Oracle query that returns all groups and users for empno 1 & 2. any help is greatly appreciated.

emp table:  
 emp_no   
 ------  
   1    
   2   
        
group table:
 group_id  emp_no  
  --------  ----
    1          1
    1          2
    2          3
    2          4
    3          5
    3          6
            
-------------
  result
-------------
 group 1  empno 1, group 1 empno 2, group 2 empno 3, group 2 empno 4 
        
         

   

CodePudding user response：

It seems the group table is enough for your assignment.
The group table causes errors because of the name. I put it in double apostrophes (Oracle 11g R2).
You can use string functions in the listagg clause.
Even for listagg, you can get certain values using a where clause. Multi-line results would use the group by clause, which is not the case.

Schema:

CREATE TABLE emp (
  emp_no number
);

CREATE TABLE "group" (
  group_id number,
  emp_no number
);

insert into emp (emp_no) values (1);
insert into emp (emp_no) values (2);

insert into "group" (group_id,emp_no) values (1,1);
insert into "group" (group_id,emp_no) values (1,2);
insert into "group" (group_id,emp_no) values (2,3);
insert into "group" (group_id,emp_no) values (2,4);
insert into "group" (group_id,emp_no) values (3,5);
insert into "group" (group_id,emp_no) values (3,6);

Select:

select
listagg ('group ' || "group".group_id || ' empno ' || "group".emp_no, ', ')
  within group (order by "group".group_id, "group".emp_no) "result"
from "group"
where "group".group_id in (1,2);

Output:

Select corresponding to your assignment:

select
listagg ('group ' || "group".group_id || ' empno ' || "group".emp_no, ', ')
  within group (order by "group".group_id, "group".emp_no) "result"
from emp left join "group" on emp.emp_no="group".emp_no
where emp.emp_no in (1,2);

The output you have requested does not match:

Edit 1:

As @MT0 have mentioned in the comment, for reversed columns emp_no and group_id in the group table there is a better result for the second select:

CREATE TABLE emp (
  emp_no number
);

CREATE TABLE "group" (
  group_id number,
  emp_no number
);

insert into emp (emp_no) values (1);
insert into emp (emp_no) values (2);

insert into "group" (emp_no,group_id) values (1,1);
insert into "group" (emp_no,group_id) values (1,2);
insert into "group" (emp_no,group_id) values (2,3);
insert into "group" (emp_no,group_id) values (2,4);
insert into "group" (emp_no,group_id) values (3,5);
insert into "group" (emp_no,group_id) values (3,6);

Output:

!!I messed the selection to reach the correct output:

select
listagg ('group ' || "group".emp_no || ' empno ' || "group".group_id, ', ')
  within group (order by "group".group_id, "group".emp_no) "result"
from emp left join "group" on emp.emp_no="group".emp_no
where emp.emp_no in (1,2);

Output: