MySQL中的联合运算符错误
我有三个mysql表。
1. inbox - ID(int), FileNo(varchar), FolioNo(smallint), Reference(varchar), Received(date), Sent(date), Description(text), Forwarded(varchar),...etc
2. outbox - ID(int), Reference(varchar), Sent(date), FileNo(varchar), FileName(varchar), FolioNo(smallint), Description(text), Receiver(varchar),...etc
3. status - ID(int), SN(int), Date(date), Minutes(varchar), Instructed(varchar), Responsible(varchar), Action(boolean), FileNo(varchar),...etc我想使用FileNo连接这三个表，并按FolioNo显示它们的顺序，如下所示。
File No: 123
File Name: abc
Folio No | Date | Description | Sender/Receiver | Minutes | Instructed | Responsible | Action
----------------------------------------------------------------------------------------------------
1 | 13-09-08 | Something | Someone | Something | Someone | Someone | Yes
2 | 13-09-10 | Something | Someone | Something | Someone | Someone | Yes
......etc我在php中的SQL查询如下：
$sql="SELECT * FROM
(SELECT I.FileNo, I.FolioNo, I.Received AS Date, I.Description, I.Forwarded
FROM Inbox I WHERE I.FileNo='$ID'
UNION SELECT O.FileName, O.FolioNo, O.Sent AS Date, O.Description, O.Receiver
FROM Outbox O WHERE O.FileNo='$ID'
UNION SELECT S.Date, S.Minutes, S.Instructed, S.Responsible, S.Action
FROM Status S WHERE S.FileNo='$ID')
AS A ORDER BY A.FolioNo";输出，
<table>
<tr><th>Folio No</th><th>Date</th><th>Description</th><th>Sender/ Receiver</th><th>Minutes</th><th>Instructed</th><th>Responsible</th><th>Action</th></tr>
<?php
while ($list = mysql_fetch_assoc($result)) {
echo "<tr><td>" . $list['FolioNo'] . "</td><td>" . $list['Date'] . "</td><td>" . $list['Description'] . "</td><td>" . $list['Receiver'] . "</td><td>" . $list['Minutes'] . "</td><td>" . $list['Instructed'] . "</td><td>" . $list['Responsible'] . "</td><td>" . ($list['Action']=1?'Yes':'No') . "</td></tr>";
$x++;
}
echo "</table>";
?>但这显示了一个错误，
注意:未定义的索引:第145行的C:\xampp\htdocs\sp\viewMinutes.php中的接收者通知:未定义的索引:第145行的C:\xampp\htdocs\sp\viewMinutes.php中的分钟通知:未定义的索引:在C:\xampp\htdocs\sp\viewMinutes.php的第145行中指示通知:未定义的索引: C:\xampp\htdocs\sp\viewMinutes.php中的Responsible通知:未定义的索引:第145行的C:\xampp\htdocs\sp\viewMinutes.php中的接收者注意:未定义的索引: C:\中的分钟xampp\htdocs\sp\viewMinutes.php第145行通知:未定义的索引:在C:\xampp\htdocs\sp\viewMinutes.php中指示的第145行通知:未定义的索引:在C:\xampp\htdocs\sp\viewMinutes.php中负责的第145行通知:未定义的索引: C:\xampp\htdocs\sp\viewMinutes.php中的接收者通知:未定义的索引:第145行的C:\xampp\htdocs\sp\viewMinutes.php中的分钟通知:未定义的索引:在C:\xampp\htdocs\sp\viewMinutes.php中指示的时间第145行通知:未定义的索引:在C:\xampp\htdocs\sp\viewMinutes.php的第145行上负责
FOLIO NO | DATE | DESCRIPTION | SENDER/ RECEIVER | MINUTES | INSTRUCTED | RESPONSIBLE | ACTION
-------------------------------------------------------------------------------------------------------------------------------------
1 | 13-09-08 | Something | | | | | Yes
2 | 13-09-08 | Something | | | | | Yes
| 13-09-08 | Something | | | | | Yes
Something| Someone | Anyone | Yes这是怎么回事？
更新：
$sql = "SELECT
Inbox.FolioNo, Inbox.Received, Inbox.Description, Inbox.Forwarded,
Outbox.FileNo, Outbox.FileName, Outbox.FolioNo, Outbox.Sent, Outbox.Description, Outbox.Receiver,
Status.Date, Status.Minutes, Status.Instructed, Status.Responsible, Status.Action
FROM Status INNER JOIN Outbox ON Status.FileNo = Outbox.FileNo
INNER JOIN Inbox ON Status.FileNo = Inbox.FileNo WHERE Status.FileNo = '$ID' ORDER BY Outbox.FolioNo ASC";回答 2
Stack Overflow用户
发布于 2013-09-11 14:25:19
您的SELECT子句没有提到别名。因此，SQL引擎不知道要生成哪些列名。我假设它采用第一个SELECT的列名。因此，您可以看到"Receiver“的未定义索引，它是see的一部分。
此外，我不明白工会背后的逻辑。您正在尝试联合具有完全不同列的3个不同表的结果。
Stack Overflow用户
发布于 2013-09-11 23:51:41
我不得不在输出视图中做了一些小的改变。现在一切都正常了。我的sql查询如下。
$sql="SELECT I.FileNo, I.FolioNo, I.Received AS Date, I.Sender AS Person, I.Description, I.Forwarded,
S.Minutes, S.Instructed, S.Responsible, S.Action FROM Inbox I
LEFT JOIN Status S ON I.ID=S.SN
WHERE I.FileNo='$ID'
UNION SELECT O.FileName, O.FolioNo, O.Sent AS Date, O.Description, O.Receiver AS Person, O.Reference, O.FileNo, O.Signed, O.Subject, O.ID
FROM Outbox O WHERE O.FileNo='$ID'";谢谢你们！
https://stackoverflow.com/questions/18733896
复制相似问题
腾讯云开发者
