Join between two tables with two rows

1
votes

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?

1
sqlperformancejoinbetweenself-join

1 Answers

1
votes

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