Help with ServerAgent Job syntax

Hi,

Hopefully someone can help me. I'm having difficulty with the syntax to delete a record from 4 joined tables when creating a job.

I have an 'Applicants' table linked to four other tables 'Courses', 'EmploymentHistory', 'Qualifications', and 'References' using the field 'ApplicantID'.

I want to create a job to delete all the records where the Finalised field = '0' and the record was created more than 3 days ago.

The syntax I have been using on just one of the joined tables to start with doesn't delete from the joined table:

USE OnlineApplications
DELETE Applicants
FROM Applicants
INNER JOIN Courses
ON Applicants.ApplicantID = Courses.ApplicantID
WHERE Finalised = 0 AND Created < DATEADD(d, 3, Created)

How can I delete the records from the other four tables?

ThanksHave you defined foreign key relationships?|||Darnit Poots, you beat me to it again!
I was going to ask for the joins between the tables :'(|||joins <> relationships ;)|||*shifty look*
I knew that ;)|||Have you defined foreign key relationships?

Nope, makes sense to do that I suppose. :o

For the projects I do, I merely create the tables then use the tables to store the data, haven't needed to create relationships in the past, bad development I know.

But I haven't really looked into the features of SQL Server yet.

I take it the syntax should work then if I create relationships?
Can I do this by creating a diagram?
Then I take it I need to set the joins to cascade to delete from other tables?|||Can I do this by creating a diagram?Yeah - but script it out.
Basic sample code. Check BoL for more options.

IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N't_name') BEGIN
DROP TABLE t_name
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'other_t_name') BEGIN
DROP TABLE other_t_name
END
CREATE TABLE dbo.other_t_name
(
c_name INT NOT NULL CONSTRAINT df_other_t_name_c_name DEFAULT 0
, CONSTRAINT pk_other_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_other_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_other_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
)
GO
CREATE TABLE dbo.t_name
(
c_name INT NOT NULL CONSTRAINT df_t_name_c_name DEFAULT 0
, CONSTRAINT pk_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
, CONSTRAINT fk_t_name_other_t_name FOREIGN KEY (c_name) REFERENCES other_t_name (c_name) ON DELETE CASCADE
)
GO

HTH