CODEWITHSUNDEEP
mysqlsqloracle-database
pdaniels0013
pdaniels0013

Reputation: 411

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!

Upvotes: 1

Views: 1318

Answers (2)

user1837296
user1837296

Reputation: 596

If I understand you correctly, you want to find projects that have both chemical tests and instrument tests.

To do that, I believe you would do:

(SELECT DISTINCT PROJECT FROM X_METER_USE WHERE INST_SNR = "INSTRUMENT") AS X
JOIN (SELECT DISTINCT PROJECT FROM X_METER_USE WHERE INST_SNR = "SNR") AS Y
ON X.PROJECT = Y.PROJECT

to get a table with a single field (project) containing those projects tested by both methods. You can then join that to your projects table to get the full information about the project if necessary (though for just the numbers you don't even need that). (You generally only want left or right joins when there's a possibility that an entry on one side doesn't have a match on the other, and you want those entries; here, you don't want untested projects or tests that don't have valid project entries, so you want an inner join.)

Upvotes: 2

are
are

Reputation: 2615

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'
)

Upvotes: 1

Related Questions