CODEWITHSUNDEEP
sqlselectjoinwhere-clause
Bham
Bham

Reputation: 319

SQL SELECT JOIN FILTER

I try to explain the problem as good as possible.

I have multiple tables:

project, group, period.

The connection table of these three is called project_status.

I will quickly show there content

Project

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

Group

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

Period

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

Project stats

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

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

i have tried the following query

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
)

Upvotes: 0

Views: 82

Answers (2)

Always Learning
Always Learning

Reputation: 302

You could use a cross apply, something like this would select projectID & name where periodID = 3:

SELECT projectID, name FROM project a 
CROSS APPLY (SELECT projectID,periodID FROM project_status WHERE projectID = a.projectID) b 
WHERE b.periodID = 3

Upvotes: 0

Andrey Rybalkin
Andrey Rybalkin

Reputation: 206

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

Upvotes: 1

Related Questions