I need to delete records from a table using inner join from other tables. See below for the table structure and records.
CREATE TABLE STUDENT (ROLL_NO NUMBER(10),NAME VARCHAR2(100),DEPT_NO NUMBER(10),JOIN_DATE DATE);
CREATE TABLE DEPART (DEPT_NO NUMBER(10), DEPT_NAME VARCHAR2(100));
CREATE TABLE ACTIVE_DEPT (DEPT_NO NUMBER(10), IS_ACTIVE VARCHAR2(1));
INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(1,'SAM',10,SYSDATE-10);
INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(2,'ALEX',10,SYSDATE -10);
INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(3,'FRANK',20,SYSDATE -10);
INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(4,'WAYNE',20,SYSDATE+1);
INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(5,'KRUL',30,SYSDATE -10);
INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(6,'ALICE',40,SYSDATE -10);
INSERT INTO DEPART (DEPT_NO,DEPT_NAME) VALUES (10,'DEPT1');
INSERT INTO DEPART (DEPT_NO,DEPT_NAME) VALUES (20,'DEPT2');
INSERT INTO DEPART (DEPT_NO,DEPT_NAME) VALUES (30,'DEPT3');
INSERT INTO ACTIVE_DEPT (DEPT_NO,IS_ACTIVE) VALUES (10,'Y');
INSERT INTO ACTIVE_DEPT (DEPT_NO,IS_ACTIVE) VALUES (20,'N');
INSERT INTO ACTIVE_DEPT (DEPT_NO,IS_ACTIVE) VALUES (30,'Y');
DELETE FROM STUDENT WHERE (STUDENT.DEPT_NO) IN (SELECT D.dept_no FROM DEPART D,ACTIVE_DEPT AD WHERE D.DEPT_NO = AD.DEPT_NO AND AD.IS_ACTIVE = 'N');
The above delete query will delete two records from the STUDENT table. Now i want to include another condition say, join_date should be a past date (join_date < trunc(sysdate)).
How to include this condition in the above delete statement. I know i can do this using a subquery but i wont prefer that option. Basically i don't have much idea about join in delete statements.
Please help.