sql - Return rows based on conditions across rows -

i not sure if such query possible. tried few different queries, none returning desired output. here requirement:

i have 2 tables:

header:  id(pk)  name 1       2       b 3       c  detail:  id(fk)  name    min     max 1       x1      1       3 1       x2      5       5 1       x3      8       9 2       x1      11      11 2       x2      13      13 2       x3      15      18 3       x1      2       2 3       x2      1       6 3       x3      9       11 

i need query given following input:

@x1 = 2 @x2 = 5 @x3 = 9 

should output:

id(pk)  name 1       3       c 

since provided values fall between min , max in detail table.

also, input of 0 indicates no range, example:

@x1=0 @x2=0 @x3=16 

should output:

id(pk)  name 2       b 

even though @x1 , @x2 0 , not fall between range, 0 here means "everything acceptable". had @x3 been 0, have outputted all: header id 1, 2 , 3, since records fall within range.

i know can using temp tables , stored procedure or processing in java; wondering if possible through direct sql

help appreciated

try this:

select distinct d1.id detail d1     inner join detail d2 on d2.id = d1.id , @x2 = 0 or @x2 between d2.min , d2.max     inner join detail d3 on d3.id = d1.id , @x3 = 0 or @x3 between d3.min , d3.max @x1 = 0 or @x1 between d1.min , d1.max 

you can wrap subquery , join other details header table.