Selecting table rows with no corresponding row in another table

Question

I'm trying to get rows from tblReqInfo when ReqID is between 5 and 8 and there is NO corresponding row in tblSomeTable

SELECT        *
FROM            tblReqInfo
WHERE         (RI_ReqID BETWEEN 5 AND 8)
             AND 
                          CASE
                                WHEN NOT EXISTS   
                                      (SELECT        CC_ReqID
                                        FROM            tblSomeTable
                                        WHERE        (CC_UserID = @CC_UserID) )
                                 THEN  1
                                 ELSE  RI_ReqID NOT IN (SELECT        CC_ReqID
                                        FROM            tblSomeTable
                                        WHERE        (CC_UserID = @CC_UserID) )
                           END 
ORDER BY RI_ReqID

Unfortunately, the query configuration wizard can't even parse this. Can anyone discern what query might work for me?

Answer 1

Try this in SQL Server:

SELECT        *
FROM            tblReqInfo
WHERE         (RI_ReqID BETWEEN 5 AND 8)
AND RI_ReqID NOT IN 
                 (SELECT        CC_ReqID
                  FROM            tblSomeTable
                  WHERE        CC_UserID = @CC_UserID)

Answer 2

The standard solution to this problem is to perform an outer join from the target table to the referent table and then grab the rows where there is no match in the referent:

SELECT target.*, referent.id
FROM   target   LEFT OUTER JOIN
       referent ON (target.id = referent.id)
WHERE  target.id BETWEEN 5 and 8
  AND  referent.id IS null
ORDER  BY target.id