title: st_distance using parents geometry
body: <p>I have two parent tables as below</p>
  
  <pre><code>CREATE TABLE GISD.CUSTOMERS
  (CUSTOMER_ID INTEGER NOT NULL, 
  FIRST_NAME VARCHAR (15) NOT NULL, 
  SURNAME VARCHAR (20) NOT NULL, 
  DATE_OF_BIRTH DATE NOT NULL, 
  HOUSE_NUMBER VARCHAR (5) NOT NULL                       
  POST_CODE VARCHAR(8) NOT NULL,
  STREET VARCHAR (25) NOT NULL,
  TOWN VARCHAR (25) NOT NULL
  );
  
  SELECT ADDGEOMETRYCOLUMN('gisd','customers', 'customers_geom', '27700','POINT',2);
  </code></pre>
  
  <p>and</p>
  
  <pre><code>CREATE TABLE GISD.CINEMAS
  (CINEMA_ID INTEGER NOT NULL,
  CINEMA_NAME VARCHAR(25) NOT NULL,
  ADDRESS_NUMBER INTEGER NOT NULL,
  POST_CODE VARCHAR(8) NOT NULL,
  STREET VARCHAR (25) NOT NULL,
  TOWN VARCHAR (25) NOT NULL,
  OPENING_TIME TIME NOT NULL,
  CLOSING_TIME TIME NOT NULL
  );
  
  SELECT ADDGEOMETRYCOLUMN('gisd','cinemas', 'cinemas_geom', '27700','POLYGON',2);
  SELECT ADDGEOMETRYCOLUMN('gisd','cinemas', 'centroid', '27700','POINT',2);
  </code></pre>
  
  <p>I have a child which uses foreign keys from both these tables as such:</p>
  
  <pre><code>CREATE TABLE GISD.BOOKING
  (BOOKING_ID INTEGER NOT NULL,
  CUSTOMER_ID INTEGER NOT NULL,
  CINEMA_ID INTEGER NOT NULL,
  TIME TIME NOT NULL,
  DATE DATE NOT NULL,
  FILM VARCHAR(50) NOT NULL, 
  BOOKING_METHOD VARCHAR (15) NOT NULL,
  BOOKING_FEE NUMERIC NOT NULL -- Numeric is suggested by postgresql.org for currency
  TICKET_PRICE NUMERIC NOT NULL
  );
  </code></pre>
  
  <p>is there a way that will allow me to take a unique booking id and reference back to the customer geometry and cinema geometry to calculate an ST_Distance? I assume some sort of nested query could do this but am having no luck?</p>
  
  <p>cheers</p>
  
  <p>UPDATE (from comment)</p>
  
  <p>I have tried the following code:</p>
  
  <pre><code>SELECT (ST_DISTANCE(
       (SELECT centroid 
        FROM GISD.CINEMAS
        INNER JOIN GISD.BOOKING ON CINEMAS.CINEMA_ID=BOOKING.CINEMA_ID
        ),(
        SELECT customers_geom
        FROM GISD.CUSTOMERS
        INNER JOIN GISD.BOOKING ON CUSTOMERS.CUSTOMER_ID=BOOKING.CUSTOMER_ID
        )
     ))
  </code></pre>
  
  <p>but get an error saying 'more than one row returned by a subquery used as an expression' Any ideas how to get round this? I ideally want it to return the distance for each booking ID</p>