Question 300915 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL – JOIN/SUB-SELECT where not all rows have the required column but some do

Hello I’m struggling with how to solve an issue and have attempted sub-selects and joins however can’t get the result I need. Below is an over-simplified database schema.

The outcome I’m looking for is to be able to provide the SUM(order_items.quantity) grouped by sessions.start_time.

Where this gets complicated is that not all entires in order_items have a valid session_id. There is one entry that does though and they have the order_id in common.

For example (order_items table assuming session_id = start_date 01-02-2024):

ID Order_id Session_id qty
1 1 1 2
2 1 NULL 5
3 2 1 2
4 2 -1 2

The intended output would be

start_date | SUM(qty)

01-02-2024 | 11

In essence I need some sort of join/sub select that can provide the SUM(order_items.quantity) grouped by sessions.start_time however can someone use the fact there is one order_items entry with a valid session_id and the rest have the order_items.order_id in common.

I’ve tried various types of join (right, left etc) to no avail

Orders

  • ID -int

Order_Items

  • ID -int
  • Order_ID -int
  • Session_ID -int
  • quantity -int

Sessions

  • ID -int
  • start_time -datetime
Tags:

2

Answers


  1. 0 
    SELECT s.start_time, SUM(oi.quantity) AS total_quantity
FROM sessions s
JOIN (
    SELECT COALESCE(oi.Session_ID, oi_common.Session_ID) AS Session_ID, oi.quantity
    FROM order_items oi
    INNER JOIN (
        SELECT Order_ID, Session_ID
        FROM order_items
        WHERE Session_ID IS NOT NULL
    ) oi_common ON oi.Order_ID = oi_common.Order_ID
) oi ON s.ID = oi.Session_ID
GROUP BY s.start_time

    This quert sum the quantities from order_items by associating valid Session_IDs and then linking them to sessions to retrieve the start_time.

    Login or Signup to reply.
  2. 0

    Try This query.

    SELECT
s.start_time,
SUM(oi.quantity) AS total_quantity FROM
Sessions s JOIN(
    SELECT
        o.Order_ID,
        MAX(s.Session_ID) AS Session_ID
    FROM
        Order_Items o
    LEFT JOIN
        Sessions s ON o.Session_ID = s.ID
    GROUP BY
        o.Order_ID
) subq ON s.ID = subq.Session_ID JOIN
Order_Items oi ON subq.Order_ID = oi.Order_ID GROUP BY
s.start_time;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search