Trying to build an SQL statement for complex search scenario

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))

SELECT a.* FROM TableA a INNER JOIN TableB ON b.id = a.id
WHERE
(b.status in (2,7))

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))

select
    *
from
    A
where
    (select count(*) from B where B.parent_id = A.id and B.status in (1,6)) > 0
and (select count(*) from B where B.parent_id = A.id and B.status not in (1,6)) = 0

select
    *
from
    A
where
    exists (select * from B where B.parent_id = A.id and B.status in (1,6))
and not exists (select * from B where B.parent_id = A.id and B.status not in (1,6))

select
    *
from
    A
where
    (@Criteria = 'Approved'
and (select count(*) from B where B.parent_id = A.id and B.status in (1,6)) > 0
and (select count(*) from B where B.parent_id = A.id and B.status not in (1,6)) = 0
    )
or  (@Criteria = 'Denied'
and (select count(*) from B where B.parent_id = A.id and B.status in (2,7)) > 0
    )
or  (@Criteria = 'Pending'
and (select count(*) from B where B.parent_id = A.id and B.status not in (2,7)) = 0
and (select count(*) from B where B.parent_id = A.id and B.status is null) > 0
    )

- If Any 2 or 7 records   => DENIED
- ElseIf Any NULL records => PENDING
- Else                    => ACCEPTED

SELECT
    [main].id,
    CASE WHEN MAX(CASE WHEN [status].value IN (2,7) THEN 1 ELSE 0 END) = 1 THEN 'DENIED'
         WHEN MAX(CASE WHEN [status].value IS NULL  THEN 1 ELSE 0 END) = 1 THEN 'PENDING'
         ELSE 'ACCEPTED' END
FROM
    [main]
INNER JOIN
    [status]
        ON [main].id = [status].main_id
GROUP BY
    [main].id

SELECT
    [main].id,
    MIN(CASE WHEN [status].value IN (2,7) THEN -1        -- Denied
             WHEN [status].value IS NULL  THEN  0        -- Pending
             ELSE                               1  END)  -- Accepted
FROM
    [main]
INNER JOIN
    [status]
        ON [main].id = [status].main_id
GROUP BY
    [main].id

DECLARE @map TABLE (
   status_value   INT,
   status_result  INT
   )

INSERT INTO @map VALUES (1,    1)
INSERT INTO @map VALUES (2,   -1)
INSERT INTO @map VALUES (6,    1)
INSERT INTO @map VALUES (7,   -1)
INSERT INTO @map VALUES (NULL, 0)

SELECT
    [main].id,
    MIN([map].status_result)
FROM
    [main]
INNER JOIN
    [status]
        ON [main].id = [status].main_id
INNER JOIN
    @map AS [map]
        ON [status].value = [map].status_value
        OR ([status].value IS NULL AND [map].status_value IS NULL)
        -- # This has been faster than using ISNULLs in my experience...
GROUP BY
    [main].id

select CASE WHEN SUM(CASE WHEN ISNULL(b.status, 0) IN (1,6) THEN 1 ELSE 0 END) = COUNT(*) THEN 'Approved'
    		WHEN SUM(CASE WHEN ISNULL(b.status, 0) IN (2,5) THEN 1 ELSE 0 END) > 0 THEN 'Denied'
    		WHEN SUM(CASE WHEN ISNULL(b.status, 0) IN (1,0,6) THEN 1 ELSE 0 END) = COUNT(*)
    			AND SUM(CASE WHEN b.status IS NULL THEN 1 else 0 END) > 0  THEN 'Pending'
    		ELSE '???' END as Status, <a column list>
FROM A
INNER JOIN b ON a.id = b.id
group by <a column list>

exists(select 1 from B where A.id = B.id and status in (1,6))
and not exists(select 1 from B where A.id = B.id and (status is null or status not in (1,6)))

SELECT
    a.*
FROM
    TableA a
WHERE
    NOT EXISTS (SELECT * FROM TableB b WHERE b.id = a.id AND b.status IN (NULL,1,6))

SELECT
    a.*
FROM
    TableA a
WHERE
    EXISTS (SELECT * FROM TableB b WHERE b.id = a.id AND b.status IN (1,6))

SELECT
    a.*
FROM
    TableA a
WHERE
    EXISTS (SELECT * FROM TableB b WHERE b.id = a.id AND b.status IS NULL)
    AND NOT EXISTS (SELECT * FROM TableB b WHERE b.id = a.id AND b.status IN (1,6))