How to get the reverse result to a sql query-CodePudding

I am using the following sql query to get the name of a branch that has submitted a report.

SELECT DISTINCT branch.b_name AS branch from branch, report_activity2

where branch.branch_id=report_activity2.branch_id 

and week =16
and year =2022

What I would also like to do is get the names of branches that have not submitted a report for the same week and year. I have tried the following query:

SELECT branch.b_name FROM branch

WHERE branch_id not in

(SELECT DISTINCT branch.b_name AS branch from branch, report_activity2

where branch.branch_id=report_activity2.branch_id 

and week =16
and year =2022)

But the second query result lists all the branches and does not exclude the branch from the first query. I am not sure if I am doing this completely wrong or I have just missed something simple. Any help to nudge me in the right direction would be appreciated.

CodePudding user response：

In the first one you don't really need distinct and also you shouldn't use the old style join anyway:

SELECT b_name AS branch from branch
where exists (select * from report_activity2
where branch.branch_id=report_activity2.branch_id
and report_activity2.week =16
and report_activity2.year =2022);

In the second one you should compare branch_id to branch_ or b_name to b_name (provided it is unique) but you were checking branch_id in (... b_bame ...). Anyway it also doesn't need a join:

SELECT b_name AS branch from branch
where NOT exists (select * from report_activity2
where branch.branch_id=report_activity2.branch_id
and report_activity2.week =16
and report_activity2.year =2022);

CodePudding user response：

can you try this mysql command ?

SELECT DISTINCT branch.b_name AS branch from branch, report_activity2

where branch.branch_id != report_activity2.branch_id 

and week =16
and year =2022 ;