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

mysql query for one-to-many inner join

I have a table someone else built to track orders and they created a separate ‘order_status’ table but I just realized that they add a new ‘status’ record every time the status of an order in production changes, rather than updating a single record.

As a result, I need to search the table order_item WHERE status_code NOT IN (140,141) AND ship_data = 0, but I also need the latest status from a table called order_item_status where the shared field is order_id

I don’t know SQL well enough to know how to build such a query. I’m assumming it is some kind of subquery with a join and a limit 1 and/or group but I don’t know how to bind the order_id between the two in such a query.

What I can show is two queries I can create:

listing orders not yet shipped or cancelled….

SELECT
    order_item.order_id, order_item.date_in, order_item.ship_date
FROM
    order_item
WHERE
    order_item.ship_date = 0 AND
    order_item_status.status_code NOT IN (140,141)
    ORDER BY order_item.date_in ASC;

Then assuming I have the order_id, I can find the most recent status for it with:

SELECT
    order_item_status.order_id, order_item_status.status_code, order_item_status.status_date
FROM
    order_item_status
WHERE
    order_item_status.order_id = :order_id
ORDER BY
    order_item_status.status_date DESC
LIMIT 1

But I have a feeling these can be done in a single query. Any help is appreciated!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I think I figured something out. If anyone can offer suggestions to improve upon this, advice is appreciated. (What I actually need is a count of items still marked as 'not shipped' and 'not cancelled'):

    SELECT
  COUNT(*) AS in_production
FROM
    order_item
    INNER JOIN (
        SELECT order_id, status_code, MAX(status_date) AS latest_status_date
        FROM order_item_status
        GROUP BY order_id
    ) latest_status ON order_item.order_id = latest_status.order_id
WHERE
    ship_date = 0 AND
    latest_status.status_code NOT IN (140,141)
    ORDER BY date_in ASC;

  2. 0

    You can use subquery and left join to achieve the result of two queries into one.

    SELECT
 oi.order_id,
 oi.date_in,
 oi.ship_date,
 ois.status_code AS latest_status_code,
 ois.status_date AS latest_status_date
FROM
 order_item AS oi
LEFT JOIN (
 SELECT
    order_id,
    MAX(status_date) AS latest_status_date
 FROM
    order_item_status
 WHERE
    status_code NOT IN (140, 141)
 GROUP BY
    order_id
 ) AS latest_status_subquery ON oi.order_id = latest_status_subquery.order_id
    LEFT JOIN order_item_status AS ois ON oi.order_id = ois.order_id AND 
    latest_status_subquery.latest_status_date = ois.status_date
    WHERE
      oi.ship_date = 0
    AND ois.status_code IS NOT NULL
    ORDER BY
      oi.date_in ASC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search