我有三个主表和一个主表,它们需要所有主表中的数据
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表中,但在mainadtatatable中,我具有基于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然后应该用托马斯和李代替
1, 'SeniorPosition', 'manager', 'Lee', 'thomas', 'kana'
2, 'JrPosition', 'Lead', 'thomas', 'Lee' , 'Billing project'
可复制
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_emp_id = 1002,则两个ID的返回值均为1002
我正在使用休眠模式。请提出一些更好的解决方案。有人可以在这里帮我吗。谢谢
您可以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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句