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_id
s 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_id
s 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
Last activity: Jun 5, 2023, 01:17 PM