sqlite3, how properly use subqueries

Question

I am beginner in the sql querying, could please explain me how can I solve such problem.

Problem, lets say I have 2 tables:

Table A: has a_id_1, a_id_2
Table_B: has b_id_1, b_id_2

and these tables has such records:

Table A: has (1,2)
Table A: has (4,5)
Table A: has (7,10)

Table B: has (1,2)
Table B: has (2,1)
Table B: has (7,1)
Table B: has (4,10)
Table B: has (1,10)
Table B: has (10,1)

So, my question is, how can I write query to delete records from Table B based on Table A, and if Table B record: b_id_1 != b_id_2 and b_id_2 != b_id_1 delete such record.

I guess I have to use subqueries, but I am not sure how properly use it in this specific case.

I am using sqlite3.

Thank you very much for your help.

Answer 1

You can use not exists, something like this:

delete from b
where not exists (select 1 from a where b.b_id_1 = a.b_id_2 and b.b_id_2 = a.b_id_1);

Your column names and examples are a bit confusing, but this is the basic idea.