Retrieving from one table where not in another table

I have two tables, contacts and deleted_contacts. I would like to pull a list of names from contacts where that contact_id does not exist in deleted_contacts.

This what I have tried that does not work:

SELECT first_name, last_name FROM contacts WHERE created_by_user_id = '" + uid + "' AND contact_id NOT EXISTS (SELECT * FROM deleted_contacts);

Any help woudl be greatly appreciated!
Thanks!
-DAGTA

SELECT first_name, last_name FROM contacts WHERE created_by_user_id = '" + uid + "' AND contact_id NOT EXISTS (SELECT * FROM deleted_contacts WHERE contact_id = your_contact_id_here);

NOT EXISTS checks to see whether the subquery that you provide returns at least 1 row. Since you were selecting everything from deleted_contacts, NOT EXISTS (SELECT * FROM deleted_contacts) always returned false. Instead, see if a row exists in deleted_contact that contains the specific contact_id that you're looking for.|||Have you tried:
AND contact_id NOT In(Select contact_id from...)|||Left join them

select Table1.ColumnName, ...
from Table1
left join Table2
on Table1.ID = Table2.ID
where Table2.ID is null

I don't like the Not In subquery thing.|||Thanks for the replies. I haven't tried the left join, yet.

The NOT IN method returns this error:

"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

To be clearer on the problem:
We have a contacts table. When a user deletes a contact, the contact is not really deleted. Instead, it's contact_id is placed in a deleted_contacts table.

I'm trying to pull a list of contacts for a user. I don't want to pull any contacts that have been 'deleted'. As such, I do not have a specific contact id. I have a created_by_user_id which is only in the contacts table, not the deleted_contacts table.

Thanks for the help!
-DAGTA|||The LEFT JOIN seems to be working. Thank you Pierre!

-DAGTA|||No problem.

I think the Not In solution is slightly faster for small resultsets, but since the Left Join method is more scalable, I tend to not worry about it too much - and I just use the Left Join method all the time.|||I see that you ended up using a LEFT JOIN, which is fine. But to elaborate a little bit on using the NOT EXISTS functionality, I have this question for you: what is the unique identifier for a given customer? Both the "contacts" and "deleted_contacts" tables have to have a relationship, or else you can't check whether a given contact is also in the "deleted_contacts" table. In other words, in the "contacts" table, you need to have a field that unique differentiates each contact from another (identity column). Suppose that's a "ContactID"field. This field has to also exist in the "deleted_contacts" table. That's how you can check, for any given record in "contacts", does it exist in the "deleted_contacts" table as well! Otherwise, you can't tie the two tables together (that's the "relational" part in Relational Databases).

By the way, use NOT EXISTS rather than NOT IN. NOT EXISTS returns true if no rows are returned by the query defined inside the NOT EXISTS () parentheses. If it returns one or more rows, it evaluates to false.

Hope this helps.

P.S. By all means, use a left join. It really doesn't matter.|||Ah yup, Not Exists is much better than Not In. I really don't like the Not In thing, even for small subqueries.

Not Exists will probably work well even on large resultsets.