Fix SQL subquery return multiple row-CodePudding

I am running this query with oracle but I get the famous oracle error ORA-01427: single-row subquery returns more than one row

Select
  DISTINCT s1.cityArrival,
  s2.cityDeparture,
  s2.sectionStartDate,
  userFirstName
FROM
  sections s1,
  sections s2,
  trajectory t,
  userInfo
WHERE
  s1.trajectId = s2.trajectId
  AND t.trajectId = s1.trajectId
  AND (
    s1.cityArrival = 'Paris'
    AND s2.cityDeparture = 'Rochelle'
  )
  AND driverMail = mailUser
  AND (
    Select
      availableSeats
    FROM
      sections s3
    WHERE
      s3.trajectId = s1.trajectId
      AND s2.sectionId <= s3.sectionId
      AND s3.sectionId <= s1.sectionId
  ) not IN (0)

Here is a schema of my dataBase :

I want to select the trajectories given a date and a city of arrival and city of departure having for each section an availableSeats > 0. The problem is that I cannot find a way to do this without for loop in sql and it becomes complicated.

SectionId is just the index of the enroute city in a proposed trajectory, that is why I can use it to get all the chuncks between two given destination.

NB: I made the design of the DB, if you have remarks on the design don't hesitate to tell me, Thanks in advance

CodePudding user response：

You can reverse the IN operands provided exactly one value 0 is to be checked.

..
  AND 0 not IN (
    Select
      availableSeats
    FROM
      sections s3
    WHERE
      s3.trajectId = s1.trajectId
      AND s2.sectionId <= s3.sectionId
      AND s3.sectionId <= s1.sectionId
  )

CodePudding user response：

The error was caused by the subquery before the IN clause. The first part returns a result set rather than an expression. Here is the syntax for using IN clause: https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm

On the other hand, you can update the last subquery in the WHERE clause into

...
AND EXISTS (SELECT 1 
      FROM
      sections s3
    WHERE
      s3.trajectId = s1.trajectId
      AND s2.sectionId <= s3.sectionId
      AND s3.sectionId <= s1.sectionId
      AND s3.availableSeats > 0
)