I have 3 tables and need to write a query: based on the request_id in the member table, I need to do the following: for each members find which group he belongs to and display the group_type and all users belong to that group. So for member [email protected], he belongs to group A and there are 2 users belong to that group ([email protected] and [email protected]). We need to display both. For member [email protected], he belongs to group B and there are no other ones belong to that group. We just display 1 record

--- the users table is used to link the members with the groups tables. users, members, groups

users tble
----------
user_id    email
1          [email protected]
2          [email protected]
3          [email protected]

create table users_tbl (id number, email varchar2);

insert into users_tbl values(1, '[email protected]');
insert into users_tbl values(2, '[email protected]');
insert into users_tbl values(3, '[email protected]');


members tble
------------
member_id    request_id  email
1               123      [email protected]
2               123      [email protected]   

create table members_tbl (member_id number, request_id number, email varchar2);

insert into members_tbl values(1, 123, '[email protected]');
insert into members_tbl values(2, 123, '[email protected]');

groups table
------------
group_id      group_type    user_id     
  1                A          1
  2                A          2 
  3                B          3

create table groups_tbl (group_id number, group_type varchar2, user_id number);

insert into groups_tbl values (1, 'A', 1);  
insert into groups_tbl values (2, 'A', 2);  
insert into groups_tbl values (3, 'B', 3);  


    
so the final result based on the data should look like this:

group_type  email
--------    ------ 
A           [email protected]
A           [email protected]
B           [email protected]

CodePudding user response：

A simple join between users_tbl and groups_tbl should do the job:

select group_type, email
from users_tbl u inner join groups_tbl g
on u.id = g.user_id

Fiddle

CodePudding user response：

I'm not sure... May be like this:

select g1.group_type, u1.email 
from 
 (select distinct g.group_type  
        from members_tbl m,
             users_tbl u,
             groups g  
     where m.request_id = 123    
       and m.email = u.email   
       and u.group_id = g.group_id) x,  
      users u1, 
     groups g1 
    where g1.group_type = x.group_type   
      and g1.user_id = u1.user_id