Sample Header Ad - 728x90

Query for records from T1 NOT in junction table T2

4 votes
1 answer
236 views
I have a table containing the names and filesystem locations of C source-files in a table, a list of relevant categories for the programming principals demonstrated by the source files in a second table and then a junction table that is a one-to-many for the categories associated with each source-file. (table definitions below question) What I need to do is get a list of file_ids from the source-file table that have no associated file_id entry in the junction table. I have done that using an except clause that relies on an inner join selecting records from the junction table where file_ids are present in the junction table, e.g.
select distinct file_id from c_file
  
  except (
    
    select distinct c_file.file_id from c_file 
      
      inner join c_file_topic_rel 
        on c_file_topic_rel.file_id = c_file.file_id 
        
        where exists (
          
          select distinct file_id from c_file_topic_rel
        
        )
  )
  
  order by file_id;
This works fine, but I'm no SQL master and I'm skeptical I'm taking a very long way around to get the records from the c_file table NOT in the junction table c_file_topic_rel. Is there a better way to go about isolating the records with no entry in the junction table? (there are about 15,000 total entries in the c_file table, if the size matters -- it's relatively small) **Table Definitions** The Default column is removed to prevent excess scrolling. If relevant, I'm happy to add the full definitions. **c_file table**
Table "public.c_file"
 Column  |            Type             | Collation | Nullable |
---------+-----------------------------+-----------+----------+
 file_id | integer                     |           | not null | ...
 fname   | character varying(64)       |           | not null |
 dirname | character varying(128)      |           | not null |
 mtime   | timestamp without time zone |           | not null | ...
 created | timestamp without time zone |           | not null | ...
Indexes:
    "cfile_pkey" PRIMARY KEY, btree (file_id)
    "cfile_fname_key" UNIQUE CONSTRAINT, btree (fname)
**c_topics table**
Table "public.c_topics"
      Column       |            Type             | Collation | Nullable |
-------------------+-----------------------------+-----------+----------+
 topic_id          | integer                     |           | not null | ...
 topic             | character varying(32)       |           | not null |
 topic_description | character varying(96)       |           | not null |
 created           | timestamp without time zone |           |          | ...
 modified          | timestamp without time zone |           |          | ...
 seq               | smallint                    |           |          |
Indexes:
    "c_topics_pkey" PRIMARY KEY, btree (topic_id)
    "c_topics_topic_key" UNIQUE CONSTRAINT, btree (topic)
Referenced by:
    TABLE "c_file_topic_rel" CONSTRAINT "c_file_topic_rel_topic_id_fkey" FOREIGN KEY (topic_id) REFERENCES c_topics(topic_id)
**c_file_topic_rel junction table**
Table "public.c_file_topic_rel"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 file_id  | integer |           | not null | 
 topic_id | integer |           | not null | 
Indexes:
    "c_file_topic_rel_file_id_topic_id_key" UNIQUE CONSTRAINT, btree (file_id, topic_id)
Foreign-key constraints:
    "c_file_topic_rel_file_id_fkey" FOREIGN KEY (file_id) REFERENCES c_file_bf(file_id)
    "c_file_topic_rel_topic_id_fkey" FOREIGN KEY (topic_id) REFERENCES c_topics(topic_id)
Asked by David C. Rankin (143 rep)
Jun 5, 2023, 08:06 AM
Last activity: Jun 5, 2023, 01:17 PM