Self join only returning one record

Question

Working on an inventory management system, and we have the following tables:

================================================
| orders | order_line_items  | product_options | 
|--------|-------------------|-----------------|
| id     | id                | id              |
| start  | order_id          | name            |
| end    | product_option_id |                 |
|        | quantity          |                 |
|        | price             |                 |
|        | event_start       |                 |
|        | event_end         |                 |
================================================

I'm trying to calculate inventory on a certain date, so I need to make a self join to compare the quantity on order_line_items to the SUM of the quantity of other records in order_line_items with the same product_option_id, and where the event start and end are within a range.

So, given a date 2016-01-20, I have:

SELECT order_line_items.id, order_line_items.product_option_id, order_line_items.order_id FROM order_line_items
WHERE order_line_items.event_end_date >= '2016-01-20 04:00:00'
AND order_line_items.event_start_date <= '2016-01-21 04:00:00'
AND order_line_items.product_option_id IS NOT NULL;

The above returns 127 rows

When I try to do a self join, like so:

SELECT 
order_line_items.id, 
order_line_items.product_option_id, 
order_line_items.order_id, 
order_line_items.quantity, 

other_line_items.other_product_option_id, 
other_line_items.other_order_id, 
other_line_items.other_quantity, 
other_line_items.total 

FROM order_line_items

JOIN (
    SELECT 
        id, 
        product_option_id AS other_product_option_id, 
        order_id AS other_order_id, 
        quantity AS other_quantity, 
        SUM(quantity) total
    FROM order_line_items
    WHERE order_line_items.event_end_date >= '2016-01-20 04:00:00'
    AND order_line_items.event_start_date <= '2016-01-21 04:00:00'
) other_line_items ON order_line_items.product_option_id = other_line_items.other_product_option_id

WHERE order_line_items.event_end_date >= '2016-01-20 04:00:00'
AND order_line_items.event_start_date <= '2016-01-21 04:00:00'
AND order_line_items.product_option_id IS NOT NULL;

It only returns 1 record. As you can see here: (https://goo.gl/BhUYxK) there are plenty of records with the same product_option_id so this last query should be returning a lot of rows

Answer 1

The added SUM(...) turns the subquery into a single row. Perhaps the subquery needed to have one of these:

GROUP BY (id)
GROUP BY (product_option_id)
GROUP BY (order_id)

(I don't know the schema or application well enough to say which makes sense.)

(Please use shorter, more distinctive, aliases; the SQL is very hard to read because of the length and similarity of order_line_items and other_line_items.)

Answer 2

Are you actually want to get the following ? :

SELECT product_option_id, sum(quantity)
FROM order_line_items
WHERE event_end_date   >= '2016-01-20 04:00:00'
AND   event_start_date <= '2016-01-21 04:00:00'
GROUP BY 1

I can not tell why you need a self join here