query problem

scialom

Hi,

I have 2 tables:

Table_job_id:
with 2 fields job_id, job name

Table_job4client
2 fields job_id,client_id

Now I try to get all the job_is from Table_job_id that ARR NOT in the other table.

How can I do it using sql?

Thanks,
Assaf

Piranha

SELECT tj.id
FROM tj
LEFT JOIN tj4c ON tj.id = tj4c.id
WHERE tj4c.id IS NULL

SELECT tj.id
FROM tj
WHERE tj.id NOT IN (SELECT DISTINCT tj4c.id FROM tj4)

scialom

Thanks for that it helps.

I try to add more conditions to this sql like:

select * from tbl_jobs, tbl_translator,tbl_translator2job LEFT JOIN 
tbl_freelancer_ex ON tbl_jobs.job_id = tbl_freelancer_ex.job_id WHERE 
tbl_freelancer_ex.job_id IS NULL and 
tbl_translator2job.translator_id=tbl_translator.translator_id and 
tbl_translator2job.job_id = tbl_jobs.job_id and 
tbl_translator.translator_status=1

but I get this error:
Unknown column 'tbl_jobs.job_id' in 'on clause'

I am sure it is just syntax. Any ideas?

Thanks,
Assaf

Piranha

Either there is no table with the exact name tbl_jobs or it does not contain a column with the exact name job_id.

scialom

This is the thing... there is tbl_jobs with job_id field.

If I write the sql like that:
select *
from tbl_jobs
LEFT JOIN tbl_freelancer_ex ON tbl_jobs.job_id = tbl_freelancer_ex.id
WHERE tbl_freelancer_ex.job_id IS NULL

it works fine... but then I need to narow it...

Assaf