How do I get unprocessed rows that have no association in Processed_Table with the least number of queries (SQLite 3)?

Nested queries will work fine.

Main_Table columns: Id

Processed_Table columns: record1_id (foreign key to id of Main_Table), record2_id (foreign key to id of Main_Table)

I want to get two records from Main_table which have not yet been processed together (based on entries in Processed_Table).

Then I process these two records and insert an entry in Processed_Table with the id of record1 as record1_id and the id of record2 as record2_id.

Then when I select the next two records from Main_Table for processing I don't want already processed records.

How do I get unprocessed rows that have no association in Processed_Table with the least number of queries (SQLite 3)?

Nested queries will work fine.

Main_Table columns: Id

Processed_Table columns: record1_id (foreign key to id of Main_Table), record2_id (foreign key to id of Main_Table)

I want to get two records from Main_table which have not yet been processed together (based on entries in Processed_Table).

Then I process these two records and insert an entry in Processed_Table with the id of record1 as record1_id and the id of record2 as record2_id.

Then when I select the next two records from Main_Table for processing I don't want already processed records.

• sql
• sqlite
• join
• foreign-keys
• relational-database

Share Improve this question Follow edited Nov 19, 2024 at 12:15 Pankaj asked Nov 18, 2024 at 11:00 PankajPankaj 7199 bronze badges 2

• -is it always the case that only 2 unprocessed records will be present in main table and not more than 2? – samhita Commented Nov 18, 2024 at 12:58
• @samhita No. Main table has many records. I just need two records that are not processed together yet (based on entries in Processed Table). I need to process all combinations of all records present in Main table one by one. Example if main table has 3 records then combinations will be 1 2, 1 3, 2 1, 2 3, 3 1, 3 2. I don't want to process a record with itself, just with all other records – Pankaj Commented Nov 18, 2024 at 13:09

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

I tried writing the query with the example data that you provided,let me know

I inserted one record in processed table(1,2) and main_table contains (1,2,3).

Fiddle is not responding at the moment so I could not share the fiddle link.

-- insert all 
--     into main_table(id) values(1)
--  into main_table(id) values(2)
--  into main_table(id) values(3)
-- select * from dual
-- ;

-- insert into processed values(1,2) ;

   SELECT m1.Id AS record1_id, m2.Id AS record2_id
FROM main_table m1
JOIN main_table m2 ON m1.Id != m2.Id
WHERE NOT EXISTS (
  SELECT 1
  FROM processed p
  WHERE 
    p.record1_id = m1.Id AND p.record2_id = m2.Id
   -- OR (p.record1_id = m2.Id AND p.record2_id = m1.Id)
  
)
;

Output looks like below as you can see 1,2 is not present in the output.