Oracle: Can't pull from data from joined table: Single-row subquery returns more than one row

Question

I have an orders table with the following fields.

order_id,
order_status_id,
ordered_date

And I have a shipments table with the following fields. (When I update order_status_id to 3 in orders table, I do have to insert order_id and shipped_at to shipments table.)

shipment_id,
order_id,
shipped_at

I want to select all from orders where order_status_id is 3. I also together want to get shipped_at from shipments table.

My try is like this:

SELECT 
    o.*, 
    s.shipped_at,
        FROM orders o
            JOIN shipments s ON s.order_id = o.order_id
        WHERE o.order_status_id = 3;

The above gives me empty results but I do have data.

SELECT 
    s.*, 
    o.*, 
    FROM shipments s
        JOIN orders o ON o.order_id = s.order_id
    WHERE s.order_id = (SELECT o.order_id FROM orders o WHERE o.order_status_id = 3);

This gives me Single-row subquery returns more than one row error.

Please help.

Answer 1

The error means that your subquery (select o.order_id...) returns more than one row, but = requires exactly 1 row. If you want to accept multiple results, use in instead. You also need to correlate your subquery with the main query.

SELECT 
    s.*, 
    o.*, 
    FROM shipments s
        JOIN orders o ON o.order_id = s.order_id
    WHERE s.order_id in (SELECT o.order_id FROM orders o WHERE o.order_status_id = 3 and o.order_id = s.order_id);

In this case, you've already joined orders, so you don't need the subquery. You can troubleshoot why you're missing data with a full outer join.

SELECT 
    s.*, 
    o.*, 
    FROM shipments s
    FULL OUTER JOIN orders o ON o.order_id = s.order_id
    WHERE o.order_status_id = 3;