Removing rows from one table based on multiple column matches from another table

[Zojak_Quaguz]

I have a table with names of people and their job types, like so:

 

Table 1 (People): [person1 varchar(25), person2 varchar(25), job1 varchar(50), job2 varchar(50)]

 

And a second table with various combinations of job types:

 

Table 2 (Jobs): [job1 varchar(50), job2 varchar(50)];

 

I want to remove all rows from the first table where BOTH jobs match a row from the second table.

 

Example:

 

Table 1:

1. John, Steve, Electrician, Plumber

2. John, Alex, Electrician, Carpenter

3. John, Sarah, Electrician, Installer

4. Steve, Alex, Plumber, Carpenter

5. Sarah, Alex, Installer, Carpenter

6. Sarah, Steve, Installer, Plumber

 

Table 2:

Electrician, Carpenter

Installer, Carpenter

Electrician, Installer

 

So the query should remove rows 2, 3, and 5.

 

I've tried a bunch of different things but it doesn't seem to work properly. The real tables are much larger (the jobs table has 5,000 or so combinations, so I can't do it manually). Any clues?

[theverychap]

Is this anywhere near?

 

SELECT * FROM Table1 WHERE job1 IN (SELECT job1 FROM Table2) OR job2 IN (SELECT job2 FROM Table2)

[DavidAM]

You can use a JOIN in a DELETE statement

 

DELETE FROM Table1 
USING Table1 JOIN Table2 
  ON Table1.job1 = Table2.job1 AND Table1.job2 = Table2.job2 

 

 

Just be careful because this will delete from BOTH tables (at least that's what the manual says):

DELETE FROM Table1, Table2
USING Table1 JOIN Table2 
  ON Table1.job1 = Table2.job1 AND Table1.job2 = Table2.job2 

 

* Always backup your data when testing DELETE statements

* This code is untested