SQL SELECT联接过滤器 [英] SQL SELECT JOIN FILTER

问题描述

我尽力解释这个问题.

我有多个表:

项目，小组，时期.

这三个连接表称为project_status.

The connection table of these three is called project_status.

我会在那里快速显示内容

I will quickly show there content

项目

| projectID | name | date |
| ------------------------|
| 1         | test | 2015 |
| 2         | test | 2015 |

组

| groupID| name |
| --------------|
| 1      | ab   |
| 2      | cd   |

期间

| periodID  | status   |
| ---------------------|
| 1         | 0        | #inactive
| 2         | 1        | #active
| 3         | 2        | #new

项目统计

| projectID | groepID | periodID |
| -------------------------------|
| 1         | 1       | 2        | #active period
| 1         | 1       | 3        | #new period

现在在gui中，您可以选择一个句点.活动期是否活跃，然后我不显示 项目，因为它正在使用(活动).现在，当我选择状态为新的期间时，必须进行检查以确定:

Now in a gui you can select a period. Is the period active then i dont show the project because it's in use (active). Now when i select a period with status new there must be a check to determine:

• 该项目是否已经进入新时期

问题是，当我编写查询时，总会有活动期.我该如何写一个仅检查项目状态中新状态的查询

The problem is when i write a query there is always the active period. How could i write a query that only checks in project status for status new

我尝试了以下查询

SELECT projectID, name
FROM project
WHERE projectID IN
(
    SELECT ps.projectID
    FROM project_status as ps
    JOIN period as per
    ON ps.periodID = per.periodID
    WHERE per.status = 0
    AND per.stats != 2
)
OR projectID NOT IN
(
    SELECT projectID
    FROM project_status
)

推荐答案

您的查询看起来正确. 只删除
AND per.periode_status！= 2 什么是periode_status?你没有解释

Your query looks right. Only remove
AND per.periode_status != 2 What is periode_status ? You didnt explain

WHERE per.status = 0
AND per.periode_status != 2 -- remove it

这篇关于SQL SELECT联接过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！