CODEWITHSUNDEEP
sqlsqlitejoinforeign-keysrelational-database
Pankaj
Pankaj

Reputation: 71

get unprocessed rows that have no association in processed table

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.

Upvotes: -1

Views: 47

Answers (1)

samhita
samhita

Reputation: 3505

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.

enter image description here

Upvotes: 1

Related Questions