How do I insert record in DB ONLY if it does not already exist?

[Luke10253]

I'm using Microsoft Access and I have 3 tables: Jobs, KSAs, and JobKSAs. JobKSAs is a linking table, i.e., it links the KSAs that are relevant to a given job.

A KSA can be linked to more than one job, but it should be linked to a single job only once. (i.e., there's no reason why the KSA "communication skill" should be linked to the job "salesperson" more than once.)

Unfortunately, I discovered that I could link a KSA to a given job as many times as I wanted, i.e., a new record would be added to Table: JobKSAs.

The KSA-Job link is created (or edited) using a form in a file entitled "add_ksa.cfm".

Table: JobKSAs contains these fields:
JKID (the primary key for this table),
frn_JobID (the foreign key pertaining to the unique ID of the given Job), and,
frn_KSAID (the foreign key pertaining to the unique ID of the given KSA).

The form data is transmitted to "ksa_au_pro.cfm", which I hope will run a check to see if a record in JobKSAs with the given frn_JobID and frn_KSAID already exists. If such a record already exists, then I don't want a new record added to Table: JobKSAs. In addition, I want the user to be taken to "ksa_already_linked.cfm", which tells the user that the KSA has already been linked to the given Job.

If a record in Table: JobKSAs containing frn_JobID and frn_KSAID does not already exist, then I want it to be added to Table: JobKSAs, and for the user to be taken to "ksa_confirm.cfm", which tells the user that the KSA has been linked to the given job.

Below is my code for "ksa_au_pro.cfm".

Thank you for any help with this!

Luke

[Luke10253 · Accepted solution]

I think I answered my own question. The code below appears to be working:


<CFIF IsDefined("FORM.JKID")>

<CFUPDATE DATASOURCE="ABC" TABLENAME="JobKSAs">

<CFLOCATION URL="ksa_confirm.cfm?JobID=#JobID#">

<CFELSE>

<!--- Check for the record first. Want to see if the KSA has already been linked to this Job. --->
<cfquery name="KSACheck" datasource="ABC">
SELECT *
FROM JobKSAs
WHERE frn_JobID = #form.frn_JobID#
AND frn_KSAID = #form.frn_KSAID#
</cfquery>

<!--- If the KSA has NOT alreay been linked, then add the linkage --->
<cfif KSACheck.recordcount EQ 0>

<CFINSERT DATASOURCE="ABC" TABLENAME="JobKSAs">

<CFLOCATION URL="ksa_confirm.cfm?JobID=#JobID#">

<cfelse>

<!--- If the KSA HAS alreay been linked, then do not add to DB and inform user --->

<CFLOCATION URL="ksa_already_linked.cfm?JobID=#JobID#">

</cfif>

</CFIF>

[Dan_Bracuk]

Here is another way to approach it. Using your topic title, How do I insert record in DB ONLY if it does not already exist?

insert into atable
(field1, field2, etc)
select distinct value1, value2 etc
from some_small_table
where
(select count(*)
from atable
where field1 = value1
and field2 = value2
etc
) = 0