How to only pull a row once from MS Query

Question

So I have a "Sample", "Test" and "Result" table linked to each other from a database and I am trying to pull information using MS Query. Each sample has one test and each test could have roughly 20 results entered by different people attached to it.

What I want is for the sample to only display if the person's name I enter is NOT involved with entering ANY of the results.

SELECT SAMPLE.SAMPLE_NUMBER, SAMPLE.TEXT_ID, SAMPLE.STATUS, SAMPLE.DATE_COMPLETED, SAMPLE.LOCATION, TEST.ANALYSIS, RESULT.ENTERED_BY

FROM DATABASE.RESULT RESULT, DATABASE.SAMPLE SAMPLE, DATABASE.TEST TEST

WHERE TEST.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND RESULT.TEST_NUMBER = TEST.TEST_NUMBER 
        AND ((TEST.ANALYSIS='ID_META' Or TEST.ANALYSIS='ID_RIBO' Or TEST.ANALYSIS='ID_BACTERIA' Or TEST.ANALYSIS='ID_MOULD') 
        AND (SAMPLE.STATUS='C') AND (SAMPLE.DATE_COMPLETED Is Not Null) 
        AND (RESULT.ENTERED_ON Between [Start Date] And [End Date]) 
        AND (RESULT.ENTERED_BY<>[Enter Name]))
ORDER BY SAMPLE.DATE_COMPLETED

This is the code that I have so far but the problem is if Alan has entered one of 10 results then that same sample will display 9 times and just not display for the one time he didn't enter a result. Is there a way that I can say if he entered ANY result at all then the sample won't appear at all.

Edit - To include additional clauses incorporated into the query. Query pulled directly from Excel connection window (from MS Query).

Gordon Linoff · Answer 1 · 2020-02-26T11:45:04.890

1

This answers the original version of the question.

You seem to be describing NOT EXISTS:

SELECT s.SAMPLE_NUMBER
FROM DATABASE.SAMPLE s
WHERE NOT EXISTS (SELECT 1
                  FROM DATABASE.RESULT r JOIN
                       DATABASE.TEST t
                       ON r.TEST_NUMBER = t.TEST_NUMBER
                  WHERE t.SAMPLE_NUMBER = s.SAMPLE_NUMBER AND 
                        R.ENTERED_ON >= DATE '2020-02-01' AND
                        R.ENTERED_ON >= DATE '2020-02-03' AND
                        R.ENTERED_BY = 'ALAN'
                 ) AND  
      S..DATE_COMPLETED Is Not Null ;

I have left in your additional conditions, even though they are not mentioned in the question.

Notes:

NEVER use commas in the FROM clause.
Always use proper, explicit, standard, readable JOIN syntax.
Use proper DATE constants in Oracle.
Don't use BETWEEN with DATE particularly in Oracle. The DATE datatype has a time component, which might not be visible when you look at the data.

edited Feb 26 '20 at 11:45

answered Feb 25 '20 at 12:14

Gordon Linoff

1,242,037
58
646
786

Hey, can you look at the updated query above with added clauses and how to incorporate them? I looked at your code and my only comments are: s, t and r don't work, I have to write the full word while in MS Query. Also is the syntax for both dates meant to be >= As for your notes: Is there a particular reason not to use commas, as MS Query automatically adds them in. And for the date issue apologies for that I was using ts {} with time and date but changed it to a parameter that pulls from an excel that recognises DD-MMM-YYYY and just forgot to change it back to the original when posting. – potterwiz Feb 26 '20 at 11:16
@potterwiz . . . No. I don't go into back-and-forth on questions. If you have a different question, you should ask it as a *new* question, not by invalidated existing answers. – Gordon Linoff Feb 26 '20 at 11:45
I just want to state I was not trying to invalidate you answer. What I was stating was your answer posted did not work for my original statement and give an error – potterwiz Feb 26 '20 at 16:06

score 0 · Answer 2 · answered Feb 25 '20 at 12:25

0

Please, try with below query:

SELECT DISTINCT(SAMPLE.SAMPLE_NUMBER) as SAMPLE_NUMBER 
FROM DATABASE.SAMPLE SAMPLE
LEFT OUTER JOIN DATABASE.TEST TEST ON TEST.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER 
LEFT OUTER JOIN DATABASE.RESULT RESULT ON RESULT.TEST_NUMBER = TEST.TEST_NUMBER
WHERE ((SAMPLE.DATE_COMPLETED Is Not Null) 
AND (RESULT.ENTERED_ON Between CAST('01-FEB-2020' as DATE) And CAST('02-FEB-2020' as DATE))
AND (RESULT.ENTERED_BY <> 'ALAN'))

answered Feb 25 '20 at 12:25

Vikram Jain

5,498
1
19
31

I have tried this but I come up with an error saying "SAMPLE"."SAMPLE_NUMBER": invalid identifier. I have double checked and there are no typos that I can see and it works in my original code fine – potterwiz Feb 26 '20 at 11:34

How to only pull a row once from MS Query

2 Answers2