加入两个MYSQL数据库表并检查第二个表是否没有记录然后返回FALSE，如果列有某个特定值，那么也是FALSE

Question

I have two tables one for participants and one for shortlisted candidate. participants table have list of all applicants for a test, and shortlisted table has only shortlisted candidate. In the second table data gets inserted once a user click on shortlist button in UI (This part is correct). And when the user click the same button twice that candidate gets removed from shortlist (I have a fields "status" in shortlisted table and a flag value removed is set in it to un-shortlist the candidate)

Now when i try to pull all candidates using the following query i am not getting the desired output.

My requirement is to pull all candidates who are not shortlisted.

P.S If a student has not been selected once, there won't be any entry for that student in shortlisted table. If a student has been selected once and the removed, there will be an entry with his detail in shortlisted table with status removed

My current query is as follows

    SELECT DISTINCT(gca.leaderEmail)lemail,
gca.leaderId,
gca.leaderName, 
gca.leaderMobile, 
gca.clgName, 
gca.t1Name, 
gca.t2Name, 
gca.id caseApplyId, 
gca.caseId caseId, 
gu.profile_pic,
        CASE WHEN gcs.id IS NULL then FALSE else TRUE end as shortlist
        FROM gmmu_case_apply gca
        LEFT JOIN gmmu_users gu ON gu.id = gca.leaderId AND gca.leaderId <> 0
        LEFT JOIN gmmu_case_shortlist gcs ON gcs.applicantId = gca.id
        WHERE gca.caseId = $caseId

Can you help me to figure out what wrong i am doing here to achieve my desired output

Update: My requirement is I should get FALSE if in second table that candidate has a status = 'removed' and also FALSE in case there is no matching JOIN for that user in Second table. I want the return of the alias shortlist either to be TRUE or FALSE I have reached some distance with this line in the query above

CASE WHEN gcs.id IS NULL then FALSE else TRUE end as shortlist

Answer 1

I believe you need to change your join on gmmu_case_shortlist to an LEFT JOIN as well. The query as you have it written is only going to return records that are in both gca and gcs.

Also, your case statement needs to be modified to check gcs.status for 'removed' like:

CASE WHEN gcs.status='removed' THEN FALSE 
     WHEN gcs.id is null THEN FALSE 
     else TRUE 
end as shortlist