我有三个主表和一个主表,它们需要来自所有主表的数据
positionmaster(position_id, position_name) values(101, 'SeniorPosition'),(102, 'JrPosition')
designationmaster(des_id, des_name) values(201, 'Manager'),(202, 'Lead')
employeeMaster(emp_id, emp_name, emp_role) values(1001, 'Thomas', 'developer'),(1002, 'Lee', 'Sales'),(1003, 'Tony', 'BA')
projectmaster(project_id, project_name, project_description) values(1,'Kana','Kana Project'),(2,'Billing', 'BillingProject')
所有员工都在employeemaster表中,但在maindatatable中,我有一个基于角色的单独列,如sales_employee_id、developer_emp_id,所有主表id都将存储在maindatatable中
maindatatables(id, position_id, des_id, sales_emp_id, developer_emp_id, project_id) values (1, 101, 201, 1002, 1001, 1), (2, 102, 202, 1001, 1002, 2)
我想写一个查询,它将返回maindatatable中的记录列表,并且需要填充名称,而不是id(在下面的数据集中,所有id的值都被替换为各自的表名值,比如如果maindatamaster表sales_emp_id有1001,developer_emp_id有1002,那么它应该被替换为thomas和Lee
1, 'SeniorPosition', 'manager', 'Lee', 'thomas', 'kana'
2, 'JrPosition', 'Lead', 'thomas', 'Lee' , 'Billing project'
Masteratatable
id position_id des_id sales_emp_id developer_emp_id project_id
1 101 201 1002 1001 1
2 101 201 1001 1003 2
查询应该返回
1, SeniorPosition, manager, Lee, thomas, kana
2, SeniorPosition, manager, thomas, tony, billingproject
这里有了内部联接,我将在下面的查询中用名称替换id:
select mdm.id, pm.project_name, dm.des_name, em.emp_name AS sales_emp_name, em.emp_name AS
developer_emp_name, prm.project_name
from maindatatables mdt
join positionmaster pm on (mdt.position_id = pm.position_id)
join designationmaster dm on (mdt.des_id = dm.des_id)
join projectmaster prm on (mdt.project_id = prm.project_id)
join employeeMaster em on (mdt.sales_emp_id = em.emp_id) //can I put a where condition here to check the role
这个查询是有效的,因为sales_emp_id和developer_emp-id都是相同的(都有1001(,但如果sales_emp_id=1001和developer_empe_id=1002,那么它将返回id的的值1002
我正在使用hibernate。请提出一些更好的解决方案。有人能在这里帮我吗。感谢
您可以第二次加入employeeMaster
:
select
mdm.id,
pm.project_name,
dm.des_name,
em1.emp_name AS sales_emp_name,
em2.emp_name AS developer_emp_name,
prm.project_name
from maindatatables mdt
join positionmaster pm on mdt.position_id = pm.position_id
join designationmaster dm on mdt.des_id = dm.des_id
join projectmaster prm on mdt.project_id = prm.project_id
join employeeMaster em1 on mdt.sales_emp_id = em.emp_id
join employeeMaster em2 on mdt.developer_emp_id = em.emp_id