Join query 1

I have 2 tables. Table A has information about inactivated print jobs and Table B has the description for Inactivation.

Note: Print jobs can be reactivated and again Inactivated. That is why Table A can have the same print job id more than once.

Now my objective is to retrieve Inactivated print job names and the reason for inactivating. Also if there is a print job that was inactivated twice, it should retrieve the most recent date of inactivation.

The following is my query:

1 SELECT PRJOB_ID, MAX(INACTIVE_DATE)AS INADAT, DESCR
2 FROM Table A, Table B
3 WHERE Table A.REASONCODE_ID=RASCODE
4* GROUP BY PRJOB_ID, DESCR

Output:

PRJOB_ID INADAT DESCR
---------- --------- ----------
AA1 03-FEB-11 NEWD DESC
AA2 05-FEB-11 ABCD
AA2 07-FEB-11 NEWD DESC
AA3 09-FEB-11 NEWD DESC

PROBLEM IS:

Along with 'AA1'and 'AA3', I ONLY need the most recently inactivated print job for 'AA2'.




Table A (TABID is pk for this table)

TABID INACTIVE_ PRJOB_ID REASONCODE
---------- --------- ---------- ----------
1 03-FEB-11 AA1 RC1
2 05-FEB-11 AA2 RC2
3 07-FEB-11 AA2 RC1
4 09-FEB-11 AA3 RC1



Table B

RASCODE DESCR
---------- ----------
RC1 NEWD DESC
RC2 ABCD