I've been saddled with a request to report on two associated tables (document and per2doc) to three different persons. Person #1 logs into the database and runs a report that shows Per2Doc documents to review (Reason Code 001). Once Person #1 has reviewed the documents his reason code gets changed to (Reason code 002,) Once his code gets changed the document becomes releasable to Person #2 showing the reason code 001 (needs review)
Once Person #2 reviews it, his reason code gets changed to 002.
Person #3 comes in and reviews only those document that are linked to Per2Doc showing the reason code 002.
I need three different queries to build a report:
1. Person 1 needs to know which docs to review (SELECT DOCUMENT.ID, PER2DOC.REASON_CODE, PER2DOC.PER_ID
FROM DOCUMENT LEFT JOIN PER2DOC ON DOCUMENT.ID = PER2DOC.DOC_ID
WHERE PER2DOC.REASON_CODE=1 AND PER2DOC.PER_ID=1
2. Person 2 needs to know what docs person 1 has reviewed but he doesn't want to see the ones he's already reviewed show up (This is where I'm stumped and this is the query I've come up with SELECT DOCUMENT.ID, PER2DOC.REASON_CODE, PER2DOC.PER_ID, PER2DOC_1.PER_ID, PER2DOC_1.REASON_CODE
FROM (DOCUMENT LEFT JOIN PER2DOC ON DOCUMENT.ID = PER2DOC.DOC_ID) LEFT JOIN PER2DOC AS PER2DOC_1 ON DOCUMENT.ID = PER2DOC_1.DOC_ID
WHERE (((PER2DOC.REASON_CODE)=2) AND ((PER2DOC.PER_ID)=1) AND ((PER2DOC_1.PER_ID)=2) AND ((PER2DOC_1.REASON_CODE)=1))
3. Person 3 needs to know which docs have been reviewed by 2 and 3 (this part is easy) but he doesn't want to see which ones he's already reviewed. This is the query (SELECT DOCUMENT.ID, PER2DOC.PER_ID, PER2DOC.REASON_CODE, PER2DOC_1.PER_ID, PER2DOC_1.REASON_CODE, PER2DOC_2.PER_ID, PER2DOC_3.REASON_CODE
FROM (((DOCUMENT LEFT JOIN PER2DOC ON DOCUMENT.ID = PER2DOC.DOC_ID) LEFT JOIN PER2DOC AS PER2DOC_1 ON DOCUMENT.ID = PER2DOC_1.DOC_ID) LEFT JOIN PER2DOC AS PER2DOC_2 ON DOCUMENT.ID = PER2DOC_2.DOC_ID) LEFT JOIN PER2DOC AS PER2DOC_3 ON DOCUMENT.ID = PER2DOC_3.DOC_ID
WHERE (((PER2DOC.PER_ID)=1) AND ((PER2DOC.REASON_CODE)=2) AND ((PER2DOC_1.PER_ID)=2) AND ((PER2DOC_1.REASON_CODE)=2) AND ((PER2DOC_2.PER_ID)=3) AND ((PER2DOC_3.REASON_CODE)=1));
My question is this: is there a better way to do this with the existing linkages in the tables? A short statement would do, and then I could get started on working it out - I don't expect anyone to do it for me, but a little advice to point me in the right direction would be greatly appreciated. I've tried to keep this as simple as possible. I will need to add extra information from other tables such as person surname, document title etc also, but I didn't want to clutter my question with all the little details.
Many thanks.
