SELECT
tblassistant.identNumber,
tblreview.ReviewID,
tblreview.Status,
tblreview.AssistantId AS AssistID,
tblassistant.Forename AS AssistForename,
tblassistant.Surname AS AssistSurname,
tblreview.Validation
FROM tblreview, tblassistant
WHERE
tblreview.Validation = 'Y'
AND tblassistant.Test <> 'Y'
AND tblassistant.identNumber = tblreview.assistantidentNumber
AND YEAR(STR_TO_DATE(tblassistant.DateDied,'%d/%m/%Y')) = '2014'
AND MONTH(STR_TO_DATE(tblassistant.DateDied,'%d/%m/%Y')) = '9';此查询使用和MySQL工作台返回以下内容
序列号助理ID审查员ID姓状态验证
4144449170 55 46杰西卡黑色全Y
4744443020 56 46莫琳加尔Y
4744443620 57 46莎拉跑完Y
4744440430 58 46路易丝惠特Y
6144448966 59 48 FIONA LEW完全Y
4444443238 60 48托尼·布莱斯Y
4744442765 61 46 OLIVIA DAVIE完全Y
但是,当我运行以下查询时:
SELECT
tblreview.AssistantIdentNumber,
tblreview.AssistID,
tblreview.Status,
tblreview.ReviewerId AS RevID,
tblAssistant.Forename AS RevForename,
tblAssistant.Surname AS RevSurname,
tblreview.Validation
FROM tblreview, tblreviewer, tblAssistant
WHERE
tblreview.ReviewerID = tblreviewer.ReviewerID
AND tblreview.Validation = 'Y'
AND tblAssistant.Test <> 'Y'
AND tblAssistant.IdentNumber = tblreview.AssistantIdentNumber
AND YEAR(STR_TO_DATE(tblAssistant.DateDied,'%d/%m/%Y')) = '2014'
AND MONTH(STR_TO_DATE(tblAssistant.DateDied,'%d/%m/%Y')) = '9';只返回:
中的5行
7行在MySQL工作台中
它不返回FREDA和ANTHONY的记录,它在查询结束时占用了大量的空间。
我尝试过FOR循环和WHILE循环。
for($i=0;$i<$rowCount;$i++)
{
$row[$i]= mysql_fetch_array($ValidationPart);
// writes out HTML table
}
for($i=0;$i<$rowCount;$i++)
{
$row[$i]= mysql_fetch_array($ValidationPart);
if (empty($row[$i]['IdentNumber']))
{
//do nothing
} else {
// Prints out HTML table
}
}我不得不匿名这其中的很多,这样查询可能不匹配。但是,除了"tblreview.ReviewerID = tblreviewer.ReviewerID“联接和相应的代码之外,它们都是相同的。
编辑:
情况会更糟。这只是一个基本的测试查询。
有一个验证名和一个经理名。两者都需要返回到一个表中。评审记录和验证记录都在同一个表中。
已经有了这样一个连接:
LEFT JOIN
(SELECT tblreview.AssistantIdentNumber,
tblreview.AssistId,
tblreview.Status,
tblreview.ManagerId AS RevID,
tblreviewer.Forename AS RevForename,
tblreviewer.Surname AS RevSurname
FROM tblreview, tblreviewer
WHERE tblreview.ReviewerId = tblreviewer.ReviewerId
AND tblreview.Status = 'Complete'
AND IsNull(tblreview.Validation)) r从答案中添加另外两个联接将使查询不返回任何内容。
必须先运行子选择联接,然后再运行其他两个连接到其他表。然后,在连接到其他表的两个联接中,必须显式引用所需的表。所以在我的例子中,主select有"tblname1 v“,然后子select是”r“。然后引用v.ID = othertbl.ID
发布于 2015-11-03 13:52:58
请设法使与LEFT JOIN的关系
SELECT
tblreview.AssistantIdentNumber,
tblreview.AssistID,
tblreview.Status,
tblreview.ReviewerId AS RevID,
tblAssistant.Forename AS RevForename,
tblAssistant.Surname AS RevSurname,
tblreview.Validation
FROM tblreview
LEFT JOIN tblreviewer
ON tblreview.ReviewerID = tblreviewer.ReviewerID
LEFT JOIN tblAssistant
ON tblAssistant.IdentNumber = tblreview.AssistantIdentNumber
WHERE
tblreview.Validation = 'Y'
AND tblAssistant.Test <> 'Y'
AND YEAR(STR_TO_DATE(tblAssistant.DateDied,'%d/%m/%Y')) = '2014'
AND MONTH(STR_TO_DATE(tblAssistant.DateDied,'%d/%m/%Y')) = '9';https://stackoverflow.com/questions/33500719
复制相似问题