Error "more than one row returned by a subquery used as an expression"-CodePudding

I have the following 3 tables:

airport

airport_id  name
1           Frankfurt
2           Paris
3           Amsterdam

area

areaid    name    airport_id
1         name1   2
2         name2   2
3         name3   3
4         name4   3

booking

id      booking_date        price   commission  areaid
1       2022-09-1T10:00     70      12          1
2       2022-09-2T11:00     60      16          2
3       2022-09-2T20:00     50      15          3
4       2022-09-3T01:00     110     15          3
5       2022-09-10T22:00    90      14          4
6       2022-09-11T19:00    65      12          1
7       2022-09-20T12:00    84      16          2

And I have this query

SELECT ar.name,
     (SELECT (b.price * b.commission) AS com
        FROM booking AS b
        LEFT JOIN area AS p ON b.areaid = p.areaid
        AND p.areaid = 3
        AND (b.booking_date >= '2022-09-01T00:00' AND b.booking_date <= '2022-09-30T23:59:59')
      )
FROM airport AS ar WHERE ar.airport_id = 2

Running the query I get the error:

more than one row returned by a subquery used as an expression

I don't understand what the problem is.

CodePudding user response：

The query

SELECT (b.price * b.commission) AS com
    FROM booking AS b
    LEFT JOIN area AS p ON b.areaid = p.areaid
    AND p.areaid = 3
    AND (b.booking_date >= '2022-09-01T00:00' AND b.booking_date <= '2022-09-30T23:59:59')

returns 7 rows. A subquery used in the SELECT list must return no more than 1 row.

A natural way to get what you want is to join all tables, e.g.:

SELECT
    ar.name,
    (b.price * b.commission) AS com
FROM airport AS ar
LEFT JOIN area AS p 
    ON ar.airport_id = p.airport_id
LEFT JOIN booking AS b 
    ON b.areaid = p.areaid
    AND p.areaid = 3
    AND b.booking_date >= '2022-09-01T00:00' 
    AND b.booking_date <= '2022-09-30T23:59:59'

Replace LEFT JOIN with JOIN to skip rows with null results.

CodePudding user response：

A subquery in the SELECT list is only allowed to return a single value. Not multiple rows, nor multiple columns. If you want any of those, the standard replacement is a LATERAL subquery. See:

What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

The more severe problem with your query is that it doesn't make sense at all.

This might be what you want, returning the list of all commissions (if any) for a given airport and a given area:

SELECT ar.name AS airport, b.com
FROM   airport ar
LEFT   JOIN LATERAL (
   SELECT round(b.price * b.commission / 100.0) AS com
   FROM   area    p
   JOIN   booking b USING (areaid)
   WHERE  p.airport_id = ar.airport_id  -- my assumption
   AND    b.areaid = 3
   AND    b.booking_date >= '2022-09-01'
   AND    b.booking_date <  '2022-10-01'
   ) b ON true
WHERE  ar.airport_id = 2;

Your subquery was uncorrelated. Assuming you really meant to link to the given airport via airport_id.

LEFT JOIN area AS p made no sense in combination with the condition WHERE p.areaid = 3. That's a hidden [INNER] JOIN. See:

SQL / PostgreSQL left join ignores "on = constant" predicate, on left table

The filter b.booking_date <= '2022-09-30T23:59:59'ma be slightly incorrect, too (unless your data is guaranteed to have a 1-minute resolution). Either way, b.booking_date < '2022-10-01' is the proper way to include "all of September". '2022-10-01' is a valid timestamp literal, short for '2022-10-01T00:00:00'.

Assuming the value in commission is really meant to be a percentage.