1

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;
1
  • 1
    JOIN users us ON ou.user_id::text = us.id::text, you need to remove , at end; also u2.email as manager_email should be us2.email; no need for cast to text (though it doesn't hurt output) Commented Nov 13, 2018 at 16:58

2 Answers 2

1

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
Sign up to request clarification or add additional context in comments.

Comments

1

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:

1 Comment

Thanks, it works. But I am accepting the other answer as that was marginally earlier. Thank you once again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.