在一个查询中返回同一个表中两个不同记录的部分

Although the actual schema is a bit more complex, the following should be a bit easier for me to get across.

虽然实际架构有点复杂,但以下内容对我来说应该更容易理解。

In one table I have a list of jobs:

在一张表中,我有一份工作清单:

Job   Status  Open date   Close date
1      Closed   04/29/2009   04/30/2009
2      Open     04/30/2009  
3      Open     04/30/2009

..and in another I have a list of notes associated with the jobs:

..在另一个我有一个与工作相关的笔记列表:

ID  Job   Type    Date       Text
1    1     Open     04/29/2009   Please fix my printer
2    1     Close    04/30/2009   Printer fixed
3    2     Open     04/30/2009   Please fix my monitor
4    2     Update   04/30/2009   Part required
5    3     Open     05/01/2009   Please fix my mouse

Each job will have an "Open" note, optionally one or more "Update" notes, and optionally a "Close" note. There is obviously a one-many relationship on the Job field.

每个作业都有一个“打开”注释,可选一个或多个“更新”注释,以及可选的“关闭”注释。在Job领域显然存在一对多的关系。

What I'd like to have is a single query that returns a list of jobs, open date, opening note and, if present, closing note, like this:

我想要的是一个单一的查询,它返回一个作业列表,打开日期,打开注释,如果存在,则关闭注释,如下所示:

Job   Status  Open date   Open note             Close note
1      Closed   04/29/2009   Please fix my printer    Printer fixed
2      Open     04/30/2009   Please fix my monitor
3      Open     04/30/2009   Please fix my mouse

My attempts at doing this always fail because I end up with the following:

我这样做的尝试总是失败,因为我最终得到以下结果:

Job   Status  Open date   Open note             Close note
1      Closed   04/29/2009   Please fix my printer
1      Closed   04/29/2009                            Printer fixed
2      Open     04/30/2009   Please fix my monitor
2      Open     04/30/2009   
3      Open     05/01/2009   Please fix my mouse
3      Open     05/01/2009

..or:

Job   Status  Open date   Open note             Close note
1      Closed   04/29/2009   Please fix my printer
1      Closed   04/29/2009                            Printer fixed
2      Open     04/30/2009   Please fix my monitor
3      Open     05/01/2009   Please fix my mouse

I'm using SQL in Access 2003 and although the eventual query will be from an Excel front-end via ADO, I'm only trying to get this working from within Access at the moment.

我在Access 2003中使用SQL,尽管最终的查询将来自Excel前端,但我只是尝试从Access中获取此功能。

3 个解决方案

#1

What 1800 Information said, but that won't work correctly without a predicate to limit the join to open and closed notes, respectively:

1800信息所说的内容,但如果没有谓词将连接限制为打开和关闭注释,则无法正常工作:

select a.job, a.status, a.opendate, 
       b.note as opennote, c.note as closenote 
from job a
     join note b on (a.job = b.job and b.type = 'Open')
     left outer join note c on (a.job = c.job and c.type = 'Closed');

#2

Essentially you need to use the Job table as the driving table for the query, then have two joins to the Note table. The first join is an inner join for the "open" note - it is an inner join because there will always be an open note. The second join is an outer join for the "close" note, since there might not be a close note. Something like this:

基本上,您需要使用Job表作为查询的驱动表,然后将两个连接到Note表。第一个连接是“打开”音符的内部连接 - 它是一个内部连接,因为总会有一个打开的音符。第二个连接是“关闭”音符的外连接,因为可能没有关闭音符。像这样的东西:

select job.job, status, opendate, opennote.note, closenote.note from
  job left inner join note opennote on opennote.job = job.job
      left outer join note closenote on closenote.job = job.job

Probably the syntax in access is slightly or massively different, but that is the general idea.

访问中的语法可能略有不同或大不相同,但这是一般的想法。

#3

The solution suggested by tpdi worked when I created the command in Access, but then threw a "join expression not supported" wobbler when I tried to edit it, or run the query via ADODB from Excel.

当我在Access中创建命令时,tpdi建议的解决方案有效,但是当我尝试编辑它时,则抛出了“不支持的连接表达式”摇摆器,或者通过Excel中的ADODB运行查询。

Closing the MDB and reopening was enough to let me run it again, but as soon as I tried to view/edit it in the SQL or QBE views it would start throwing exceptions. Eventually I could not open or edit the query, even after a "compact & repair" - presumably some sort of internal corruption within Access.

关闭MDB并重新打开就足以让我再次运行它,但是当我尝试在SQL或QBE视图中查看/编辑它时,它会开始抛出异常。最终我无法打开或编辑查询,即使在“紧凑和修复”之后 - 可能是Access中的某种内部损坏。

Strangely though, I was able to get it working by referencing the query (SELECT * FROM noteReport;) - weird. It appears that my initial success was a fluke, as I was not able to recreate the exact query and have it work again.

奇怪的是,我能够通过引用查询(SELECT * FROM noteReport;)来实现它 - 很奇怪。看来我最初的成功是侥幸,因为我无法重新创建确切的查询并让它再次起作用。

However, I was eventually able to replicate the desired report with the following:

但是,我最终能够使用以下内容复制所需的报告:

SELECT a.callReference, b.callPriority, datevalue(INT(a.openedDT)),
  b.callerFirstName & ' ' & b.callerSurname AS Caller, c.noteText, d.noteText 
FROM ((tblMain AS a 
  INNER JOIN tblCalldetails AS b ON a.callreference=b.callReference) 
  LEFT JOIN tblNotes AS c ON a.callReference=c.callReference) 
  LEFT JOIN tblNotes AS d ON a. callReference =d.callReference
WHERE a.status = 'Closed' 
  AND c.noteType='Open' 
  AND d.noteType='Closing' 
  AND INT(a.closedDT) = #5/1/2009#;

Posting this here as much as for my benefit as for anyone else that may find this via Google.

这里发布的内容与我可以通过Google找到的其他任何人一样受益。

#1