Deleting records in a many to many relationship

[Bam Bam]

Hi all,

I have a music database with two main tables (tblArtists and tblRecordings)
joined by a junction table (tblLinkArtist_Recording) used to resolve the
many to many relationship (as an Artist can have many recordings and a
Recording(Various Artists CD for example) can have many Artists). Each
record in the junction table contains only the ArtistID and the RecordingID
as a composite primary key, no other fields. I would like to be able to
delete the associated Recordings when I choose to delete an Artist.

Assuming 'strArtistID' is holding the ArtistID of the current Artist record:

I can delete the Artist with this:

DoCmd.RunSQL "DELETE * FROM tblArtists WHERE ArtistID=" & strArtistID

And I can delete the Artist from the junction table with this:

DoCmd.RunSQL "DELETE * FROM tblLINKArtist_Recording WHERE ArtistID= " &
strArtistID

But the next command is obviously useless as tblRecordings doesn't store an
ArtistID, it is stored in the junction table:

DoCmd.RunSQL "DELETE * FROM tblRecordings WHERE ArtistID=" & strArtistID

What kind of SQL string or Query can I use to managed this deletion? Or am I
going about this in the wrong way?

Yours faithfully,

Limited Intellect (but 17 gold medals heading down under!!)

 

[Douglas J. Steele]

Delete from tblRecordings before you delete from tblLINKArtist_Recording:

DELETE * FROM tblRecordings WHERE RecordingID IN
(SELECT RecordingID FROM tblLINKArtist_Recording WHERE ArtistID=" &
strArtistID)

 

['69 Camaro]

Hi.

Your variable name for strArtistID indicates to most programmers that it
contains a string value. The example syntax that you gave indicates that it
is a numerical value, which could confuse future programmers who may
maintain your application. But just in case your strArtistID variable
actually contains a string value, then I'd like to add to Doug's answer
because you'd want to use the following syntax, instead:

DoCmd.RunSQL "DELETE * FROM tblRecordings WHERE RecordingID IN
(SELECT RecordingID FROM tblLINKArtist_Recording WHERE ArtistID='" &
strArtistID & "');"

And bear in mind that if the tblRecordings record is deleted for one artist
who is one of many who made the recording, then this tblRecordings record
will be deleted for _all_ of those artists, unless you have referential
integrity enforced.

For example, if you have three records in the tblArtists table, one each for
Peter, Paul and Mary, and have the record for "Leaving On A Jet Plane" in
the tblRecordings table, and have three records in your junction table, each
matching one of these three artists to "Leaving On A Jet Plane," then delete
Paul from the tblArtists table and then run this delete query, you'll remove
the "Leaving On A Jet Plane" record from the tblRecordings table, unless you
have referential integrity enforced.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Any human can read my reply E-mail address and should alter it so that a
message will be forwarded to me. Spammers are free to use my UNALTERED
reply E-mail address. I will *never* get those messages!)

 

[Bam Bam]

Thanks for the help gentleman,

Yes, I think the variable should be an integer I guess. Is the SQL string
you suggest an example of a junction query?

 

['69 Camaro]

Yes, I think the variable should be an integer I guess.
Then for the sanity of the programmers who follow you in maintaining this
database application, consider changing the variable name to intArtistID or
iArtistID or nArtistID so that other programmers can tell at a glance that
this variable does not require the modified syntax necessary for a string
value in a SQL statement.

Is the SQL string you suggest an example of a junction query?

A junction query? I'm not familiar with that term. I'd call it a DELETE
query that contains a subquery in the WHERE clause.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Any human can read my reply E-mail address and should alter it so that a
message will be forwarded to me. Spammers are free to use my UNALTERED
reply E-mail address. I will *never* get those messages!)