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', '[email protected]');
insert into users (id, email) values ('222', '[email protected]');
insert into users (id, email) values ('333', '[email protected]');
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. [email protected], [email protected] are two normal users. [email protected] 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;
JOIN users us ON ou.user_id::text = us.id::text,you need to remove,at end; alsou2.email as manager_emailshould beus2.email; no need for cast to text (though it doesn't hurt output)