SQL Select Statement output - return rows conditionally to the second table

Question

I could use some help with the following SQL Select script:

I need to select all the rows from the table conditionally, depending on whether the user ID has already entered data in the second table with the same identifier.

Example:

a) Select all rows from table A for idNumber, where idNumber is not in table B b), but for each idNumber that IS is in TABLE B, still return a row if the specific user ID is not in this 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 entries 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 thread, but I hope this makes sense.

Thanks so much in advance, ukjezza !!.

Answer 1

 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 

Answer 2

It seems that LEFT JOIN and COALESCE can take care of this:

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

Answer 3

 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'