Subquery returned more than 1 value SQL Error

Question

I am receiving the error below when I try and execute this query.

What I'm trying to do is if the user ID belongs to the supplier (from_user_id) then display the SUPPLIER description otherwise display the CUSTOMER description.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

 SELECT status_history.date,
       status_history.doc_type,
       status_history.document_id,
       user.email,
       doctypes.description,
       orders.order_no
       document_no,
       (SELECT CASE
                 WHEN status_history.id = orders.from_id THEN
                 old_status.supplier_description
                 ELSE old_status.customer_description
               END AS old_status_desc
        FROM   status_history
        WHERE  status_history.link_number = 821
               AND status_history.id = user.id
               AND status_history.doc_type =
                   doctypes.doc_type
               AND status_history.old_status_id = old_status.status_id
               AND status_history.new_status_id = new_status.status_id) AS
       old_status_desc,
       (SELECT CASE
                 WHEN status_history.id = orders.from_id THEN
                 new_status.supplier_description
                 ELSE new_status.customer_description
               END AS new_status_desc
        FROM   status_history
        WHERE  status_history.link_number = 821
               AND status_history.id = user.id
               AND status_history.doc_type =
                   doctypes.doc_type
               AND status_history.old_status_id = old_status.status_id
               AND status_history.new_status_id = new_status.status_id) AS
       new_status_desc
FROM   status_history,
       user,
       doctypes,
       qc_status old_status,
       qc_status new_status,
       orders
WHERE  status_history.link_number = 821
       AND status_history.id = user.id
       AND status_history.doc_type = doctypes.doc_type
       AND status_history.old_status_id = old_status.status_id
       AND status_history.new_status_id = new_status.status_id
       AND orders.quote_id = status_history.document_id
       AND status_history.doc_type = 1
ORDER  BY status_history.date,
          status_history.doc_type  

EDIT:

The results of this query is used to display a modal with the different statuses for an order. So it shows for an order something like New -> Saved.

I have two types of users SUPPLIERS and CUSTOMERS, as a supplier you can see the supplier's view of a status description and vice versa.

An example would is if I viewed an order's status as a supplier it will display Saved -> Sent and if I viewed it as a customer it should appears Saved -> Received.

Whenever an order is created it inserts a record in the status_history table with one record for the SUPPLIER and one for CUSTOMER. I then use the user id against the order.from_user_id to determine which type of user it is to get the right description.

Answer 1

You can get rid of the subquery, and use a CTE instead like this:

 WITH CTE
 AS
 (
     SELECT status_history.id, CASE
            WHEN status_history.id = orders.from_id THEN
            old_status.supplier_description
            ELSE old_status.customer_description
        END AS old_status_desc,
        CASE
            WHEN status_history.id = orders.from_id THEN
            new_status.supplier_description
            ELSE new_status.customer_description
        END AS new_status_desc
     FROM   status_history
     WHERE  status_history.link_number = 821
       AND status_history.id = user.id
       AND status_history.doc_type =  doctypes.doc_type
       AND status_history.old_status_id = old_status.status_id
       AND status_history.new_status_id = new_status.status_id
 )
 SELECT status_history.date,
       status_history.doc_type,
       status_history.document_id,
       user.email,
       doctypes.description,
       orders.order_no
       document_no,
       c.old_status_desc,
       c.new_status_desc
FROM   status_history
INNER JOIN user ON status_history.id = user.id
INNER JOIN doctypes status_history.doc_type = doctypes.doc_type
INNER JOIN qc_status old_status ON status_history.old_status_id = old_status.status_id
INNER JOIN qc_status new_status ON status_history.new_status_id = new_status.status_id
INNER JOIN orders ON orders.quote_id = status_history.document_id
INNER JOIN CTE AS c ON status_history.id = c.id
WHERE  status_history.link_number = 821 AND status_history.doc_type = 1
ORDER  BY status_history.date,
          status_history.doc_type;

This will remove the error message,but you will have duplicated statuses for each row. In this case you might need to use an aggregate function with group by to get only one status for each row.