join query

[Priti_P]

Hello there,
I have problem as:
have two tables Candidate and candidate_interview
for perticular vacancy_ID , Candidate's status becomes "In Proces" and when his interview is schedule ,
entry is added in candidate_interview table againt that vacancy with status In Process.

now what i want is ,
such candidates whose staus is in process but they are not in candidate_interview table yet.
(Means in short thire interview is not scheduled yet)
Pleas help mw with query.

[pritaeas]

Please provide some sample data, and expected output.

[Priti_P]

Thanks for such quick reply !!

CANDIDATE table is
Cand_id,name,status,vacancy_id

Interview_schedule_table contains
Cand_id,panel_id,date,Interview_for_vacancy_id

now what i want is such candidate whose status=In Process
and Vacancy_id=123
and candidate is present in CANDIDATE table but not in Interview_schedule_table

hope i am clear
:(

[pritaeas]

SELECT * 
FROM `candidate`
WHERE `vacancy_id` = 123
AND `status` = 'In Process'
AND `cand_id` NOT IN (
    SELECT DISTINCT `cand_id` 
    FROM `interview_schedule_table`
)

[Priti_P]

It worked..
I was doing sillyyyy mistake
Thanks a lot
BUT
can I do this by using LEFT JOIN query??
if then how?

[pritaeas]

Similar, left join on vacancy and use the where to remove rows containing the candidate.