Question on Joining

I am having difficulty understanding the different joins.

I have two tables with these fields.

users
-- userid
-- department

applications
-- userid
-- application name

The users table would have unique ids such as

bill - Accounting
fred - Accounting
joe - HR

The applications table will have duplicate id's such as

bill - paychecks
bill - payables
fred - payables
fred - billing
joe - hiring


I am trying to add everyone from one department to the applications table, but only if they are not already listed in the applications table. The first step is to pull the list of id that I can insert. In the above example, if I selected the Accounting department and billing application, bill needs to be found, but fred and joe should not be on the list.

I have tried several different join statements, but I got lost trying to pull only those people that match the department and do not have that specific id/application entry in the applications table.

I suppose that I could do this by pulling a list of everyone in Accounting and then doing a query for each of them to see if the id/application combination is already listed, but I would rather do it in one query if possible.

I have tried looking on mysql.com, but I could not make much sense of the join section.

Is there a join statement that will do this?

Thanx for any help you can give.

JustLearning

~~~ Pretend that I have a long and creative signature ~~~

Try this

select users.userid from users where users.userid not in (select distinct userid from applications);

If applications is a large table this will be really slow. This is Oracle syntax so you may have to alter it for your database type.


Rita

Thank you for you answer. Unfortunately, MySQL does not support sub-selects unless part of an insert or delete query. I will see if I can use this to make something that will work, but it does not look promising.

Thank you anyway.

JustLearning