Join query
Join query
(OP)
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
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
RE: Join query
SELECT PRJOB_ID,
INACTIVE_DATE AS INADAT,
DESCR
FROM Table_A AS A,
Table_B AS B
WHERE A.REASONCODE_ID = B.RASCODE
AND A.INACTIVE_DATE = (SELECT MAX(INACTIVE_DATE)
FROM Table_A
WHERE PRJOB_ID = A.PRJOB_ID)
Untested.
Note that if a PRJOB_ID has several rows for its most recently date, all of them will be returned.
RE: Join query
please do not cross post
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Join query