postgres join queries

Question

I have 3 postgresql tables as follows

employees

emp_name | emp_id | user_name | psw | etc

work

Work_id |work_type| Project_manger_id(employee) | Architect_id (emp) | tech_lead_id | dept_id

dept

dept_id | dept_name

now what I want is a table like this

work_type | project_manager (name)  | architect (name) | tech_lead (name ) | dept (name )

Simply, what I want is to get the work table but replacing id's with names

what I'm stuck on is , I don't know exactly how to merge work table with employee , since I hv to get employee names matching , project_manager_id with emp_id , architect_id with emp_id , tech_lead_id with emp_id , how can I do this , so far what I did was some thing like this SELECT e1.emp_name, e2.emp_name , e3.emp_name , w.work_id FROM employees e1, employees e2, employees e3,work w Where e1.emp_id_id = w.project_manager_id AND e2.emp_id = w.architect_id AND e3.emp_id = w.tech_lead_id ; — Lakshitha Udara
– Lakshitha Udara, Commented May 28, 2012 at 5:12
in this way I can get it done , but only problem is is there is no value for a employee , then whole tuple is ignored by this , :( — Lakshitha Udara
– Lakshitha Udara, Commented May 28, 2012 at 5:16
Very similar to a recent MySQL question (with suggestions equally applicable to PostgreSQL). — Andriy M
– Andriy M, Commented May 28, 2012 at 7:18

Sudhakar B · Accepted Answer · 2012-05-28 05:06:23Z

3

Try this

Select WT.Work_Type,PM.emp_name As ProjectManager,AR.emp_name AS Arhitect,TL.emp_name As  TechLead,Dept.Dept_name As Department From
Work 
INNER JOIN employees AS PM ON (Wt.Project_manger_id=PM.emp_id)
INNER JOIN employees AS AR ON (Wt.Architect_id=AR.emp_id)
INNER JOIN employees AS TL ON (Wt.tech_lead_id=TL.emp_id)
INNER JOIN Dept As Dept ON (Wt.dept_id=Dept.dept_id)

answered May 28, 2012 at 5:06

Sudhakar B

1,57311 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres join queries

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related