OMD wrote:what datatype are _Position columns?
I have two tables:
Positions_tbl (Start_Position, End_Position)
X_Positions (X_Name, X_Position)
I need to find how many X_Position lies between each Start_Position, End_Position
Solomon Yakobson wrote:Shouldn't that be count(x.x_position) rather than count(*)? Count(*) will show 1 when it should be 0.
Use outer join:
select p.start_position, p.end_position, count(*) from positions_tbl p left join x_positions x on x.x_postion between p.start_position and p.end_position group by p.start_position, p.end_position order by p.start_position, p.end_position /