tags:

views:

37

answers:

1

Hi All,

I'm stuck with this problem I need help. I have 3 tables in MSAcess as follows:

Table1 has two columns a JN and SN, Table2 and Table3 only have one column a SN. On Table1 the JN is allowed to have duplicates. What I would like to do is to delete the JN and all SN associated with the JN. So for example if Table1 SN contain the following entries 10,10,10,11,11 and under the SN it has 1,2,3,4,5 and Table2 under SN contains 1,2,3,4,5 and Table3 under SN contains 1,2,3,4,5. I would like to delete all records with JN=10 and SN 1,2,3 from Table1 and SN 1,2,3 from Table2 and SN 1,2,3 from Table3. I don't have JN in the other tables otherwise it would be easy to do by just referencing the JN on all the tables.

Thanks,

A: 
DELETE t2 FROM Table2 t2
INNER JOIN Table1 t1 on t1.SN = t2.SN
WHERE t1.JN = 10 AND t1.SN IN (1,2,3)

DELETE t3 FROM Table2 t3
INNER JOIN Table1 t1 on t1.SN = t3.SN
WHERE t1.JN = 10 AND t1.SN IN (1,2,3)

DELETE FROM Table1 WHERE JN = 10 AND SN IN (1,2,3)
joschi