我的数据库有以下表格结构:
-
tbl_project
-
tbl_员工
- tbl_delivery
- 用户_可交付
其中tbl_prjct和tbl_delivery具有一对多关系,tbl_employee和tbl_deliverable具有许多关系,因此它们被拆分为user_to_deliverable表。
我想要一个查询来显示项目名称(来自tbl_project)、项目的可交付成果(来自tbl_delivery)和特定可交付成果分配给的员工名称。
我能得到写这个查询的帮助吗?
您想要的查询非常像89.67%,如下所示:D
SELECT a.ProjectName,
b.deliverables,
d.employeename
FROM tbl_project a
INNER JOIN tbl_deliverable b
ON a.projectID = b.projectID
INNER JOIN user_to_deliverable c
ON b.recordID = c.RecordID
-- or could be the primary key
INNER JOIN tbl_employee d
ON c.userID = d.userID
在不了解架构的情况下近似解决方案。如果您希望为每个项目连接可交付成果/员工,最好是检索结果并用非SQL语言(PHP、Python或您正在使用的任何语言)进行解析。如果您希望在SQL中完全完成此操作,则需要使用GROUP_CONCAT()
。
select tbl_project.name as project,
tbl_deliverable.name as deliverable,
tbl_employee.name as employee
from tbl_project
join tbl_deliverable
on (tbl_project.id = tbl_deliverable.project_id)
join user_to_deliverable
on (tbl_deliverable.id = user_to_deliverable.deliverable_id)
join tbl_employee
on (user_to_deliverable.employee_id = tbl_employee.id)