How NOT to display records that are in both tables?

I have two tables,

A1:

ID NAME SIGNUP
1  John 14.04.2011
2  Mike 14.04.2011
3  Gabe 14.04.2011
4  Jane 15.04.2011

and B1:

ID SIGNUP
1  14.04.2011
4  15.0开发者_JS百科4.2011

How can I fetch only those records which are in the left table (A) but NOT in the right one (B) based on ID and SIGNUP? The result I'm looking for is this:

ID NAME
2  Mike
3  Gabe

This is the query I came up with but it's not giving the desired result:

SELECT A1.ID, A1.NAME
FROM A1 
LEFT JOIN B1 ON A1.SIGNUP=B1.SIGNUP
AND A1.ID<>B1.ID 
WHERE A1.SIGNUP=TO_DATE('14.04.2011','DD.MM.YYYY')

SELECT a.*
 FROM a LEFT JOIN b USING (signup) 
 WHERE b.id IS NULL AND *signup thingie*;

though this is the way to select rows that have no match I do not see how you want to get the desired result from these tables, I believe you missed something in your illustration.

MINUS saves the day:

SELECT A1.ID, A1.NAME
FROM A1 
MINUS
SELECT A1.ID, A1.NAME
FROM A1 
JOIN B1 ON A1.SIGNUP=B1.SIGNUP

You could try using:

SELECT A1.ID, A1.NAME
FROM A1 
WHERE A1.ID not in (SELECT B1.ID FROM B1)
AND A1.SIGNUP=TO_DATE('14.04.2011','DD.MM.YYYY')

If the signups for corresponding IDs in A1 and B1 are always the same, then it's simply:

select a1.id,a1.name,
from a1
left join b1
on b1.id=a1.id
where b1.id is null

SELECT  a1.*
FROM    a1
WHERE   (id, signup) NOT IN
        (
        SELECT  id, signup
        FROM    b1
        )
        AND signup = TO_DATE('14.04.2011', 'DD.MM.YYY')