Complicated SQL issue - please help

[palehorse]

Hello,

I have two tables structured like so:

(top are fields, bottom is data)

TABLEA
job_id,user
100,user1
102,user1
103,user2
101,user2

TABLEB
job_id
100
101
102
103

Table A represents all the current jobs the user has
Table B represents all the jobs available to the users

User logs in and sees a list of jobs from TABLEB and accepts two of them and those are stored in TABLEA. Now, those two jobs are not removed from TABLEB since more than one user can accept a job; however, I do not want them to see those two specific jobs from TABLEB - but others who do not currently have those job_id in TABLEA

So - according to how TABLEA looks at the top -

User1 would only see jobs 101 and 103
User2 would only see jobs 100 and 102

If there was a thrid user, they would see all the jobs since TABLEA doesn't have a record of him having jobs.

Now, my question is - how the heck to I do that?

I tried something like this:
(say the MyReader is associated with the first Select statement

select * from TABLEA 
string.format("select * from TABLEB where job_id<>'{0}'", MyReader("job_id"))

This doesn't work though - can anyone help me out here -

Respectfully,

Scott

 

[TechGnome]

SELECT * FROM TableB WHERE job_id NOT IN (SELECT job_id FROM TableA WHERE user = '{0}')

-tg

 

[palehorse]

It works perfect - I didn't realize I could write it out like that - thanks.