Join query 1

[rivi10]

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

 

[JarlH]

[tt]
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)[/tt]

Untested.

Note that if a PRJOB_ID has several rows for its most recently date, all of them will be returned.

 

[r937]

see solution here --

http://www.tek-tips.com/viewthread.cfm?qid=1638118&page=1

please do not cross post

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[rivi10]

Thanks JarlH!