pairing query

[Stan Denman]

In my DB I have a document type "CPYEVREQ" for a given source that needs to have a companion "MER". The former is a request for medical records, and the latter document type the fulfillment of that request. So "source" is going to be the doctor or clinic for which we want medical records. This code will pair these request and responses up nicely: "SELECT PDFParentBookmark.DocumentType, PDFParentBookmark.DocumentSource, PDFParentBookmark_1.DocumentSource, PDFParentBookmark_1.DocumentTypeFROM PDFParentBookmark AS PDFParentBookmark_1 INNER JOIN PDFParentBookmark ON PDFParentBookmark_1.DocumentSource = PDFParentBookmark.DocumentSource


WHERE (((PDFParentBookmark.DocumentType)="CPYEVREQ") AND ((PDFParentBookmark_1.DocumentType)="MER"));
"

BUT I want to identify the document types "CPYEVREQ" for which we do NOT have a matching MER.

[June7]

SELECT * FROM PDFParentBookmark WHERE DocumentSource NOT IN (SELECT DocumentSource FROM PDFParentBookmark WHERE DocumentType = "MER") AND DocumentType = "CPYEVREQ"

Is DocumentSource enough to associate records? Can't doctor or clinic have multiple requests? Don't you also need patient ID? Even then, can there be multiple requests for same patient?

[isladogs]

EDITED:
Whilst using NOT IN should give you the correct output, it will be very slow if you have a large dataset as that approach cannot make use of indexing.
There are are two alternatives: NOT EXISTS and a simple unmatched query (using the wizard) both of which should be far faster as they do make use of any indexes in the join / filter fields

For more information, see my article Finding Unmatched Records

[jojowhite]

SELECT * FROM 
(SELECT 
        PDFParentBookmark.DocumentType, 
        PDFParentBookmark.DocumentSource, 
        PDFParentBookmark_1.DocumentSource, 
        PDFParentBookmark_1.DocumentType, 
FROM PDFParentBookmark AS PDFParentBookmark_1  
LEFT JOIN PDFParentBookmark ON PDFParentBookmark_1.DocumentSource = PDFParentBookmark.DocumentSource 
WHERE (((PDFParentBookmark.DocumentType)="CPYEVREQ") AND ((PDFParentBookmark_1.DocumentType)="MER"))) 
WHERE ((DocumentType) Is Null);

[isladogs]

Just for info, the suggested answers in posts #2 and #4 are both invalid SQL statements

[jojowhite]

oooh, that sql is not tested.

this one is tested:

SELECT *
FROM PDFParentBookmark
WHERE (((PDFParentBookmark.DocumentSource) Not In  
(SELECT 
        PDFParentBookmark.DocumentSource AS Source  
FROM PDFParentBookmark AS PDFParentBookmark_1  
INNER JOIN PDFParentBookmark ON PDFParentBookmark_1.DocumentSource = PDFParentBookmark.DocumentSource 
WHERE PDFParentBookmark.DocumentType="CPYEVREQ" AND PDFParentBookmark_1.DocumentType="MER")));

[jojowhite]

post #3 sql is valid but does not produce the desired result.

[isladogs]

Touché
I had tested mine and thought it gave the required output.
As mentioned I see no reason for a self join in this case but perhaps I’m misunderstanding the purpose.

EDIT: Ignore that. Just read post #1 properly. Out at the moment. Will look again later when I’m at my computer

[Stan Denman]

Thanks for your response. That works!

[Stan Denman]

Good points all. I am trying to locate which requests for medical records have not been responded to. You are correct that my logic is based upon an assumption that there will be only one request for medical records for a given source. For example, lets say in fact records were request from Dr. Death multiple times, and he finally honors the request. Since we would have one MER and two CPYEVREQ this code would still "flag" the request as unfulfilled when in fact it was fulfilled. Would have been nice if these requests were dated but they are not. However, it is true we could have if there is an MER for a given source with a page number that is higher than a CPYEVREQ, then that CPYEVREQ cannot be consider as "unfulfilled".

[isladogs]

OK - sorry about my earlier incorrect comments about self joins. I've now removed those

I can confirm that the subquery in post #6 works but I would still avoid using NOT IN for the reasons I explained in post #3
If you want to use a subquery, NOT EXISTS is more efficient (but I find that harder to write correctly)

An alternative approach that is usually the most efficient of all is an unmatched query

For example, start with the query from post #1 using aliases to shorten the SQL and save as qryMatchedPairs.

SELECT A.*, B.DocumentTypeFROM PDFParentBookmark AS A INNER JOIN PDFParentBookmark AS B ON A.DocumentSource = B.DocumentSource
WHERE (((B.DocumentType)="MER") AND ((A.DocumentType)="CPYEVREQ"));

Now do an unmatched query based on the table and qryMatchedPairs:

SELECT T.*FROM PDFParentBookmark AS T LEFT JOIN qryMatchedPairs ON T.DocumentSource = qryMatchedPairs.DocumentSource
WHERE (((qryMatchedPairs.B.DocumentType) IsNull));

That gives the same output as the subquery in post #6 (Checked!)

It could be written as a single query but it would be harder to read (and the OP already has the matched pairs query anyway!)

[June7]

Which works?

I fixed the SQL in post 2.

A "FIND UNMATCHED" is not so simple in this case because only 1 table is involved - this means a self-join and in this case I think a nested query is still needed.

Colin, your link in post 3 isn't working.

[isladogs]

@June7
Thanks for the prompt. Link in post #3 now fixed

I agree that its easier to write a NOT IN query in this case. However, as previously stated its very inefficient as it cannot make use of indexed fields
That's why I prefer, if possible, to use an unmatched query even if I use two stacked queries to achieve the result.

[Stan Denman]

Thanks so much Colin!!