SQL Select Statement issue - returning rows conditionally on a 2nd table

I could really use some help with the following SQL Select statement scenario:

I need to select all rows from a table conditionally depending on whether a userID has already entered data into a second table with the same ID.

Example:

1. Select all rows from TABLE A for idNumber where idNumber not in TABLE B
2. but for each idNumber that IS in TABLE B, still return row unless a specific userID is in that row in TABLE B.

TABLE A
========
idNumber|type|Date

   1      A    01/01/01

   2      A    01/01/01

   3      B    01/01/01

   4      B    01/01/01

   5      B    01/01/01

TABLE B
========
idNumber|type|userID
   1      A    0000

   3      B    0000

   4      B    1111

userID to exclude records for = 1111

SQL Query should return:

idNumber|type|Date

   1      A    01/01/01

   2      A    01/01/01

   3      B    01/01/01

   5      B    01/01/01

Apologies for the long winded post but i hope it makes sense.

Many thanks in开发者_如何转开发 advance, ukjezza.!!

Select idNumber, type, Date
From TableA
Where Not Exists    (
                    Select 1
                    From TableB
                    Where TableB.idNumber = TableA.idNumber
                        And TableB.userID = 1111
                    )

Another choice:

Select TableA.idNumber, TableA.type, TableA.Date
From TableA
    Left Join TableB
        On TableB.idNumber = TableA.idNumber
            And TableB.userId = 1111
Where TableB.idNumber Is Null

Looks like a LEFT JOIN and COALESCE could take care of it:

SELECT a.* 
FROM TableA as a
LEFT JOIN TableB as b
ON a.idNumber = b.idNumber
WHERE COALESCE(b.userID, -1) != 1111

select A.*
from TableA as A
  left outer join TableB as B
    on A.idNumber = B.idNumber
where B.idNumber is null or
      B.userID <> '1111'