Having issues and it's certainly my SQL ability. Using an access database and I'd like to transition to using R for my queries (I use it to do the statistical analysis). I set up the tables in Access, so it could be related to the table structure (which was probably not done as a competent DBA would do it).

Anyway, I have an Analysis table which links to a Sample table, a Compound table (chemical) and an AnalysisType table (run by different chromatography systems). I want to query multiple "types" of values to get data that may be worth analyzing statistically. So for instance, I want to query two AnalysisTypes (say index 2 and 6), a single Compound (say index 6) and a that fall within certain ranges. I thought this should work:

SELECT * 
  FROM Analysis 
 WHERE (AnalysisType = 2 
   AND Compound = 6 
   AND AnalysisMean BETWEEN 100 AND 200) 
   AND (AnalysisType = 6 
   AND Compound = 6 
   AND AnalysisMean BETWEEN 200 AND 400)

I get no records. If I change the AND between the parentheses to OR, I get the records I expect (but extra ones): There will be records where one of the two conditions is met - but not both. I want the instances when both analyses of the same compound fit within the given ranges. Perhaps that's not possible? If I have OR, I get two records returned with the two conditions met. However, I get returns where both conditions are not met... Is there syntax that makes the AND between parenthesis in my code function as I'd expect???

Having issues and it's certainly my SQL ability. Using an access database and I'd like to transition to using R for my queries (I use it to do the statistical analysis). I set up the tables in Access, so it could be related to the table structure (which was probably not done as a competent DBA would do it).

Anyway, I have an Analysis table which links to a Sample table, a Compound table (chemical) and an AnalysisType table (run by different chromatography systems). I want to query multiple "types" of values to get data that may be worth analyzing statistically. So for instance, I want to query two AnalysisTypes (say index 2 and 6), a single Compound (say index 6) and a that fall within certain ranges. I thought this should work:

SELECT * 
  FROM Analysis 
 WHERE (AnalysisType = 2 
   AND Compound = 6 
   AND AnalysisMean BETWEEN 100 AND 200) 
   AND (AnalysisType = 6 
   AND Compound = 6 
   AND AnalysisMean BETWEEN 200 AND 400)

I get no records. If I change the AND between the parentheses to OR, I get the records I expect (but extra ones): There will be records where one of the two conditions is met - but not both. I want the instances when both analyses of the same compound fit within the given ranges. Perhaps that's not possible? If I have OR, I get two records returned with the two conditions met. However, I get returns where both conditions are not met... Is there syntax that makes the AND between parenthesis in my code function as I'd expect???

• sql
• syntax

• 1 Please provide some sample input data and the expected result. And tag the RDBMS you use. – Jonas Metzler Commented Nov 20, 2024 at 12:42
• Please note that it's possible AnalysisType == 2 OR AnalysisType == 6, but it is impossible for AnalysisType to be 2 AND simultaneously 6 – Dmitrii Bychenko Commented Nov 20, 2024 at 13:34

1 Answer 1

You can rewrite your WHERE clause as follows

SELECT * FROM Analysis 
WHERE AnalysisType = 2 AND Compound = 6 AND AnalysisMean BETWEEN 100 AND 200 
AND AnalysisType = 6 AND Compound = 6 AND AnalysisMean BETWEEN 200 AND 400

by removing all the brackets, as they are just separated by AND both inside and outside of brackets. As you can see you require AnalysisType=2 and AnalysisType=6. They cannot be both true at the same time and they are only separated by AND clauses, so the result of your WHERE clause will always be false, therefore returning no rows.

Next to that, you require AnalysisMean to be both between 100 and 200 as well as between 200 and 400. As the BETWEEN operator is inclusive on both ends, this would only be a match at the exact value of 200, which is probably not the usage yousage you expected.

Your fix should be using OR, making it

SELECT * FROM Analysis 
WHERE (AnalysisType = 2 AND Compound = 6 AND AnalysisMean BETWEEN 100 AND 200) 
   OR (AnalysisType = 6 AND Compound = 6 AND AnalysisMean BETWEEN 200 AND 400)

This matches any rows that have either AnalysisType 2 in combination with Compound 6 and an AnalysisMean between 100 and 200, or rows that match your second condition. You do describe that you want rows that match both conditions, but that is not possible with the conditions as stated above. I would recommend reading a bit further on the AND and OR operator in this link.