Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009

    Question Unanswered: Help With Querying

    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!!

  2. #2
    Join Date
    Nov 2006
    I don't think you understand the error message.

    Each SELECT returns a set o rows. If you write
    point.position > (SELECT ...)
    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 ...)
    wich means
    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);

  3. #3
    Join Date
    Aug 2009
    Olympia, WA
    It may be easier for OP to understand the problem and be better for performance (depending on the number of rows) if you used MIN and MAX instead.

    SELECT point.position
    FROM point, line
    WHERE point.position > (
      SELECT MAX(line.start) FROM line
    AND point.position < (
      SELECT MIN(line.stop) FROM line

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts