Hello. I am a complete newbie to Posgres. I need to use postgres just for a small area of a project I am doing. I have a few tables, for example: the 'line' table with integer properties 'start' and 'stop'. And another table called 'point' with a 'position' integer property.
So, now i need to find all 'points' which lie within 'lines', ie. find all the objects from the 'point' table whose 'position' lies between the 'start' and 'stop' properties of the 'line' table.
Here is my query:
select point.position FROM point WHERE point.position > (SELECT line.start from line) AND point.position < (SELECT line.stop FROM line);
I'm getting an error as follows:
ERROR: more than one row returned by a subquery used as an expression
which i believe means that more than one result is being returned, which is true, according to my data, more than one result should be obtained. So how do i go about actually retrieving these multiple results? Please help!!
you must realized that you compare here a scalar value (piont.position) with a set of values (Select statement). Everything is ok for postgres if select returns no more than one value, but if not there is an error that tells you can't compare one value with a set of values (there is not such operator in maths).
But you can use
point.position > ALL(SELECT ...)
point.position > ANY(SELECT ...)
1) greater than a maximum value returned by SELECT statement
2) greater than a minimum value returned by SELECT statement
The same for '<' operator.
In this situation you can write (if I understand what do you want to achieve , my english is not perfect )
select point.position FROM point,line WHERE point.position > ALL(SELECT line.start from line) AND point.position < ALL(SELECT line.stop FROM line);