Cfrgtkky

I have the following database structure, with postgres 9.5:

create table users (

  id character varying(255), 

  email character varying(127)

);



create table org_users(

  id character varying(255), 

  user_id character varying(255), -- foreign key to users.id

  manager_org_user_id character varying(255) -- refers to org_users.id; same table referential dependency

);



insert into users (id, email) values ('111', 'user1@example.com');

insert into users (id, email) values ('222', 'user2@example.com');

insert into users (id, email) values ('333', 'manager@example.com');



insert into org_users (id, user_id, manager_org_user_id) values ('o1', '111', 'o3');

insert into org_users (id, user_id, manager_org_user_id) values ('o2', '222', 'o3');

insert into org_users (id, user_id, manager_org_user_id) values ('o3', '333', null);

I have three users in my table. user1@example.com, user2@example.com are two normal users. manager@example.com is their manager. Now I want to display the following structure:

org_user_id, employee_user_id, employee_email, manager_org_user_id, manager_user_id, manager_email

I tried with the following sql query but it fails to work for me with syntax error near JOIN. Any help ?

select ou.id as employee_org_user_id,

    us.id as employee_user_id, 

    us.email as employee_email, 

    ou.manager_org_user_id as manager_org_user_id,

    u2.email as manager_email

from org_users ou 

    JOIN users us ON ou.user_id::text = us.id::text,

    LEFT JOIN org_users ou1 ON ou1.id::text = ou.manager_org_user_id::text

    LEFT JOIN users us2 ON ou1.user_id::text = us2.id::text;

Remove space, rename u2.email, drop the cast to text:

select ou.id as employee_org_user_id,

    us.id as employee_user_id, 

    us.email as employee_email, 

    ou.manager_org_user_id as manager_org_user_id,

    us2.email as manager_email

from org_users ou 

    JOIN users us ON ou.user_id = us.id

    LEFT JOIN org_users ou1 ON ou1.id = ou.manager_org_user_id

    LEFT JOIN users us2 ON ou1.user_id = us2.id

This query resolve your problem:

SELECT ou.id as employee_org_user_id,

       us.id as employee_user_id, 

       us.email as employee_email, 

       ou.manager_org_user_id as manager_org_user_id

FROM org_users ou JOIN users us ON ou.user_id = us.id

Live example:

• http://tpcg.io/PuMym5