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.

Please provide some sample data, and expected output.

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
:(

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.