I am trying to build an SQL Statement for the following search scenario:
I have trying to return all of the columns for an individual record for Table A based on the value of the status column in Table B. Each record in table A can have multiple rows in table B, making it a one to many relationship. The status column is nullable with a data type of integer.
Here are the possible values for status in table B:
- NULL = Pending,
- 1 = Approved,
- 2 = Denied,
- 6 = Forced Approval,
- 7 = Forced Denial
The end user can search on the following scenarios:
- Approved - All table B records must have a value of 1 or 6 for status.
- Denied - One table B record must have a value of 2 or 5. Any other records can have 1,6, or null.
- Pending - All table B records can have a value of 1,6 or null. One record must be null because it is not considered completed.
UPDATE
I consulted with one of our DBAs and he developed the following solution:
Approved:
SELECT a.* FROM TableA a INNER JOIN TableB ON b.id = a.id
WHERE
(b.status in (1,6) and b.status IS NOT NULL) AND
b.id NOT IN (SELECT id from TableB WHERE status IS NULL)
AND b.id NOT IN (SELECT id from TableB WHERE status in (2,7))
Denied:
SELECT a.* FROM TableA a INNER JOIN TableB ON b.id = a.id
WHERE
(b.status in (2,7))
Pending:
SELECT a.* FROM TableA a INNER JOIN TableB ON b.id = a.id
WHERE
(b.status IN (1,6) OR b.status IS NULL)
AND b.id NOT IN (SELECT b.id FROM TableA a INNER JOIN TableB b ON b.id = a.id WHERE (b.status IN (1,6) AND b.status IS NOT NULL) AND b.id NOT IN (SELECT id from TableB WHERE status IS NULL))
AND b.id NOT IN (SELECT id FROM TableB WHERE status IN (2,7))
UPDATE 2:
@Micth Wheat - How would I refactor the following solution using the EXIST/NOT EXIST t-sql keyword?