pdaniels0013 pdaniels0013 - 1 month ago 9
MySQL Question

Oracle SQL Query Help finding entries that a column has two possible values

I apologize if this question isn't formatted correctly, I normally don't ask SQL questions. I have the following query created for my oracle database

SELECT * FROM X_METER_USE x
LEFT JOIN INSTRUMENTS i on x.ID_NUMBER = i.NAME and x.INST_SNR = 'INSTRUMENT'
LEFT JOIN STANDARD_REAGENT sr ON x.ID_NUMBER = sr.SAMPLE_NUMBER AND x.INST_SNR = 'SNR'
WHERE x.INST_SNR = 'SNR' AND sr.SAMPLE_NUMBER = i.NAME;


The high level idea is that entries in this
X_METER_USE
table correspond to a test engineers in my organization have performed in a project. They performed the test with a measuring instrument from the
INSTRUMENT
table or a chemical from the
STANDARD_REAGENT
table. In an
X_METER_USE
entry, if the
INST_SNR
column has the value "INSTRUMENT," they used an instrument to perform the test, if the value is "SNR" they used a chemical to perform the test. Each
X_METER_USE
entry is associated with a project number stored in a column called
PROJECT
. The relationship here is one project can have many
X_METER_USE
entries associated with it. So the
PROJECT
column has duplicate entries corresponding to all the tests performed on that project.

Here is the table:

enter image description here

And here are some example entries

enter image description here

I need a query to find a few project numbers that the engineers have entered tests with both chemicals and instruments so I can test a front end web interface and make sure my logic there is correct. Thanks in advance!

are are
Answer Source

I would use intersect as simplest way to check what you want

select * from X_METER_USE 
where project in (
    SELECT x.project FROM X_METER_USE x
    INNER JOIN INSTRUMENTS i on x.ID_NUMBER = i.NAME 
    where x.INST_SNR = 'INSTRUMENT'
    INTERSECT
    SELECT x.project FROM X_METER_USE x
    INNER JOIN STANDARD_REAGENT sr ON x.ID_NUMBER = sr.SAMPLE_NUMBER 
    where x.INST_SNR = 'SNR'
)