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

 

[bborissov]

SELECT TableA.*,TableB.Descr
FROM TableA
INNER JOIN TableB ON TableA.REASONCODE_ID = TableB.RASCODE
INNER JOIN(SELECT PRJOB_ID, MAX(INACTIVE_DATE) AS INADAT
                  FROM TableA
           GROUP BY  PRJOB_ID) Tbl1
      ON TableA.PRJOB_ID      = Tbl1.PRJOB_ID AND
         TableA.INACTIVE_DATE = Tbl1.INADAT

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[rivi10]

works perfect! Thanks Borislav.