Join between two tables with two rows-CodePudding

I have two tables:

Table A:

Name	Value
ABC	95
XYZ	85

Table B:

Category	Value
MaxVal	90
MinVal	80

I want to achieve this result 85 is between 80 and 90 value, so we filter out only XYZ and not ABC :

Result
XYZ

I'm trying as below:

SELECT Name as Result from TableA a, TableB b, TableB c 
where a.value < b.value and a.value > c.value 
and b.value=c.value and 1=1;

Is there a better way to do this?

CodePudding user response：

Get rid of b.value=c.value, which is filtering out all records and select proper categories from tables b and c:

SELECT Name as Result from TableA a, TableB b, TableB c 
where a.value < b.value and b.category = 'MaxVal'
and a.value > c.value and c.category = 'MinVal';

Here's another option, in my opinion more readable:

SELECT Name as Result FROM TableA
 WHERE a.value > (SELECT value FROM TableB WHERE category = 'MinVal')
   AND a.value < (SELECT value FROM TableB WHERE category = 'MaxVal');