Hereâs the scenario: I have two tables, Positions and Assignments. Positions is the full inventory of available positions in my organization. Assignments where organization members are tied to their assigned position. Assignments may have a status of either âQueuedâ, âCurrentâ, or âClosedâ. The business rule is that we cannot assign positions that are in either a Queued or Current status.
I am trying to build a report that will display all positions available for assignment. I essentially need to do the Corteza equivalent of this:
SELECT * FROM positions
WHERE recordID NOT IN (SELECT positionID FROM assignments WHERE assignmentStatus = âCurrentâ OR assignmentStatus = âQueuedâ)
Assistance appreciated.
Unfortunately, this solution wonât work. I need to get the positions that either donât have Current or Queued matches in the Assignments table, or else donât appear at all (have never been assigned).
If we canât do this with NOT IN, I think we need the equivalent of a Left Outer Join (the current Join functionality only seems to do Inner Joins). Then I suppose the query would look like:
SELECT DISTINCT positionTitle
FROM
(SELECT positions.positionTitle AS positionTitle, assignments.assignmentStatus AS assignmentStatus FROM positions
LEFT OUTER JOIN assignments ON positions.ID = assignments.positionID) AS joinPositionAssignment
WHERE assignmentStatus != âCurrentâ AND assignmentStatus != âQueuedâ
I want the query to return positions that have either NULL or âClosedâ for assignmentStatus.
So, it looks like I would need LEFT OUTER JOIN and a switch for SELECT DISTINCT.