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

MySql select query to filter out record if field has multuple NULL entries for same SN

I have a master item ship/return table where we keep track of every SN shipped (date it was shipped, where it was shipped to, and if the SN was returned). We have some records that show a SN was shipped, then shipped again without the previous return info. This is because someone entered the SN as shipped when it actually was not shipped. I need to write a MySQL query that will show all shipped SN’s but not include the record without the return data when it was shipped after.

Here is an example of the data from my query:

Select * From database1.Deployed_Returned group by SN, RMA_No ORDER BY SN, Date_Shipped;
SN Date_Shipped Ship_to_Store Date_Received Received_from_Store RMA_No
1111 4/28/2021 LA_7146 6/9/2021 LA_7146 12356
1111 6/22/2021 Denver_2114 8/18/2021 Denver_2114 12557
1111 8/20/2021 NY_7890 NULL NULL NULL
1111 9/29/2021 Miami_9821 11/30/2022 Miami_9821 13029
1111 12/5/2022 Atlanta_1278 NULL NULL NULL
2222 5/12/2022 Chicago_3476 11/24/2022 Chicago_3476 12987
2222 12/5/2022 Denver_2114 NULL NULL NULL
3333 1/20/2023 LA_7146 NULL NULL NULL

SN: 1111, Date_Shipped: 8/20/2021, Date_Received: NULL. This record needs to be removed (because the next record shows it shipped on 9/29/2022)

Tags:

2

Answers


  1. 0

    You are looking for a
    self join.

    To begin, create a temp reporting table that will simplify your life:

    CREATE TABLE returned AS
SELECT *
FROM database1.Deployed_Returned
GROUP BY  SN, RMA_No ORDER

    Giving it a proper index wouldn’t hurt, either.
    Sometimes CREATE TABLE … followed by INSERT INTO …
    is the most convenient way to arrange that.

    Now identify bogus records according to your business logic.
    The OP did not explain exactly what that is.
    Here is one take on it.
    Possibly RMA_No should also be involved.

    SELECT r1.*
FROM returned r1
JOIN returned r2  ON r1.SN = r2.SN
WHERE r1.Date_Received IS NULL
  AND r2.Date_Received IS NOT NULL

    You may also find it helpful to display selected r2 details
    such as the date received.

    Verify the business logic, and then
    use those result rows to DELETE rows that are bogus.
    You may find it helpful to add an is_bogus boolean
    column, so you can try some UPDATE commands and
    then finally when you’re confident of the result do a
    DELETE … WHERE is_bogus;

    Login or Signup to reply.
  2. 0

    If you are using MySQL 8.0 you can use CTE – 13.2.20 WITH Common Table Expressions.

    So, at the first step you find maximal Date_Shipped for each SN which is the date of latest shipped and not returned item.

    Then you find records for each item where Date_Received is NULL but Date_Shipped is less then maximal value for found item.

    WITH
    last_shipments AS (
        SELECT MAX(Date_Shipped) AS Date_Shipped, SN
        FROM Deployed_Returned
        GROUP BY SN
    )
SELECT * FROM Deployed_Returned AS d
    JOIN last_shipments AS l ON d.SN = l.SN;
    WHERE d.Date_Received = NULL AND d.Date_Shipped < l.Date_Shipped
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search