如何在查询结果中忽略一次记录时完全忽略该记录 [英] How to completely ignore a record when it is ignored once in the query results

问题描述

亲爱的开发人员，

我正在开发一个设备数据库，我的一些同事可以添加项目并为项目分配必要的设备。这种系统的一个主要特征当然是看一件设备是否可用于一个项目。我有
做了一个涉及三个数据库的应用程序：一个项目数据库，一个设备数据库和一个事务数据库（一个链接到项目数据库ID的列和一个链接到设备数据库的列）。添加或更改项目时，
用户可以选择单击按钮，以便通过表单将设备分配给项目。在这种形式中，我想要一个只显示项目时可用设备的查询。这意味着如果一件设备没有
在该期间的开始日期和结束日期之间进行交易，则该设备可供使用。 

I am developing an equipment database where some of my coworkers can add projects and assign the necessary equipment to the project. One of the main features of such a system is of course to see if a piece of equipment is available for a project. I have made an application involving three databases: a project database, an equipment database and a transactions database (a column linked to the ID of the project database and a column linked to the equipment database). When a project is added or changed, the user will have the option to click a button in order to assign equipment to the project via a form. In this form I would like to have a query which only shows the available equipment at the time of the project. Meaning that if a piece of equipment does not have transactions between the start date and the end date of the period, the piece of equipment is available to use. 

I写了一个处理这个问题的查询，但是，我对这个查询有一个问题，在搜索了几乎完整的互联网之后，我仍然没有找到解决方案。问题是，如果查询运行，它会检查项目的开始和结束
日期之间的事务，如果没有事务，则设备显示为可用，如预期的那样。如果该特定设备只有一个交易且它位于项目的开始和结束日期之间，那么设备
也不会显示，也如预期的那样。

I wrote a query which handles this, however, I have one problem with this query and after searching almost the complete internet, I still haven't found a solution. The problem is that if the query runs it checks for transactions between the start and end date of the project, if there are no transactions, the piece of equipment is shown as available, as expected. If there is only one transaction for that particular piece of equipment and it is between the start and the end date of the project the piece of equipment is not shown, also as expected.

但是，如果设备有多个交易，其中一个交易位于项目的开始日期和结束日期之间，并且至少有一个（历史性）交易不在开始和结束之间项目日期，该项目的设备仍然是
。我的问题是，如果有人知道这个解决方案，这意味着如果一件设备被忽略一次，因为至少有一个交易在项目的开始和结束日期之间，那么特定的设备
将被完全忽略，没有显示。 

However if the piece of equipment has multiple transactions where one is between the start and the end date of the project and there is at least one (historic) transaction not between the start and the end date of the project, the piece of equipment is still shown as available for the project. My question is if anyone knows a solution for this, meaning that if a piece of equipment is ignored once because at least one transaction is between the start and end date of a project, the particular piece of equipment is ignored completely and is not shown available. 

我搜索了很多并尝试了很多，你们基本上是我对这个功能的最后希望。我希望你能给我一些提示或解决方法来解决这个问题。

I have searched a lot and tried a lot and you guys are basically my last hope for this function. I hope you are able to give me some tips or workarounds to solve this.

提前感谢你的时间和精力，

Thanks in advance for your time and effort,

亲切的问候，

Jordy

推荐答案

但是，如果设备有多个交易，其中一个在项目的开始日期和结束日期之间，并且至少有一个（历史性）交易不在项目的开始和结束日期之间，那么设备的可用价格仍为
。我的问题是，如果有人知道这个解决方案，这意味着如果一件设备被忽略一次，因为至少有一个交易在项目的开始和结束日期之间，那么特定的设备
将被完全忽略，没有显示。 

However if the piece of equipment has multiple transactions where one is between the start and the end date of the project and there is at least one (historic) transaction not between the start and the end date of the project, the piece of equipment is still shown as available for the project. My question is if anyone knows a solution for this, meaning that if a piece of equipment is ignored once because at least one transaction is between the start and end date of a project, the particular piece of equipment is ignored completely and is not shown available. 

您好Jordy，

您的意思是当设备已经耦合时在项目中，您不想再在可用设备列表中看到它？

Do you mean that when an Equipment is already coupled to a Project, you dont want to see it anymore in the list of available Equipment?

在这种情况下，您可以添加设备可能没有当前项目的交易的选择。

In that case you can add in the selection that the Equipment may not have a Transaction for the current Project.

Imb。

这篇关于如何在查询结果中忽略一次记录时完全忽略该记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！