所以这是我的问题。我有多个表需要从中提取数据
项目表
项目id项目名称etc
项目需求表
需要id需要
项目需求表
项目id需要id
志愿者桌(又名会员(
所有成员数据
member_skills表
member_idskill_id(又称项目需求(
这是我的问题:
$query = "INSERT INTO PROJECT_NOTIFY_TEMP (project_id, project_name, volunteer_name, volunteer_email, volunteer_skills)
SELECT p.project_id as PROJECT_ID, p.project_name as PROJECT_NAME, CONCAT(m.first_name,' ',m.last_name) as FULL_NAME, m.email as EMAIL, GROUP_CONCAT(DISTINCT pn.need ORDER BY pn.need SEPARATOR ', ') as NEED
FROM PROJECTS p
JOIN project_needs_to_projects pntp
ON (pntp.project_id = p.PROJECT_ID)
JOIN project_needs pn
ON (pntp.need_id = pn.need_id AND pn.active = 'Y')
JOIN member_skills ms
ON (pn.need_id = ms.skill_id)
JOIN members m
ON (ms.member_id = m.member_id)
WHERE p.PROJECT_ID = '".$PROJECT_ID."'
GROUP BY m.member_id";
它正确地提取了所有信息,但我一直无法弄清楚如何提取所有项目需求。现在,它只满足了我有一个志愿者匹配的需求。基本上,我想提取这些(作为技能(和项目需求(作为需求(
如果没有一些样本数据和预期输出,很难回答。
如果project_needs
的所有值都不在project_needs_to_projects
表中,则可能发生这种情况。当您使用JOIN
时,它会尝试返回JOIN
左侧和右侧两个表中具有匹配值的记录。
将RIGHT JOIN
用于project_needs
而不是JOIN
将返回project_needs
中的所有值以及project_needs_to_projects
表中的匹配值+空值。您的查询将看起来像
SELECT p.project_id as PROJECT_ID, p.project_name as PROJECT_NAME,
CONCAT(m.first_name,' ',m.last_name) as FULL_NAME, m.email as EMAIL,
GROUP_CONCAT(DISTINCT pn.need ORDER BY pn.need SEPARATOR ', ') as NEED
FROM PROJECTS p
JOIN project_needs_to_projects pntp
ON (pntp.project_id = p.PROJECT_ID)
RIGHT JOIN project_needs pn
ON (pntp.need_id = pn.need_id AND pn.active = 'Y')...