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?

It's an "mdf" file. I think it was created as an "SQL Server Database." — elbillaf, Dec 31 '13 at 16:29

score 1 · Answer 1 · answered Dec 31 '13 at 16:31

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)

answered Dec 31 '13 at 16:31

Abe Miessler

82,532
99
305
486

That doesn't work. Still get an error on execute. – elbillaf Dec 31 '13 at 17:00
What error do you get? – Abe Miessler Dec 31 '13 at 17:10
Ah, sorry. Moved on to fix some other problems. This is my last one. The error is " Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." Here's the thing: I can execute this query in the designer view and it correctly returns no rows. – elbillaf Dec 31 '13 at 22:05

score 1 · Answer 2 · answered Dec 31 '13 at 16:32

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

Selecting table rows with no corresponding row in another table

2 Answers2