CODEWITHSUNDEEP
mysql
Maxima
Maxima

Reputation: 352

self join providing wrong answers

Hypothetical data - tbl1:

orderID SupplierID Status Reason Created At
29 1 22-01-2021 22:08
29 2 22-01-2021 22:10
29 265 3 23-01-2021 06:25
29 2 sometext 23-01-2021 12:25
29 1605 3 24-01-2021 10:21
29 1605 4 anothertext 24-01-2021 11:03
29 324 3 26-01-2021 06:43
29 2 sometext 26-01-2021 12:43
29 1564 3 26-01-2021 16:09

Desired result:

orderID SupplierID Status Reason Created At
29 265 3 23-01-2021 06:25
29 324 3 26-01-2021 06:43

My query -

select distinct tbl1.orderID, tbl1.created_at, tbl2.supplierID 
from tblxyz as tbl1 left join tblxyz as tbl2
on tbl1.orderID = tbl2.orderID 
where tbl1.status=2 and tbl1.reason='sometext' and tbl2.status=3 and tbl1.created_at < (tbl2.created_at + INTERVAL 1 DAY)
group by tbl2.supplierID

I am unable to figure out where is my query wrong.

Upvotes: 0

Views: 33

Answers (2)

Akina
Akina

Reputation: 42622

Do you need in this:

SELECT t2.*
FROM tbl1 t1
JOIN tbl1 t2 USING (orderID)
WHERE t1.Status = 2
  AND t2.Status = 3
  AND t1.Reason = 'sometext'
  AND t2.Created_At BETWEEN t1.Created_At - INTERVAL 1 DAY AND t1.Created_At 
ORDER BY t1.Created_At;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=543d9150d1b23a01df01e0223f3fb3f2

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

You can try to use LAG window function to get previous status and reason, then do your judgment.

Schema (MySQL v8.0)

CREATE TABLE tblxyz(
   orderID int,
  SupplierID INT,
  Status INT,
  Reason VARCHAR(50),
  CreatedAt DATETIME
  
);


INSERT INTO  tblxyz VALUES (29,NULL,  1,'','2021-01-22 22:08');
INSERT INTO  tblxyz VALUES (29,NULL,  2,'','2021-01-22 22:10');
INSERT INTO  tblxyz VALUES (29,265  , 3,'','2021-01-23 06:25');
INSERT INTO  tblxyz VALUES (29,NULL,  2,'sometext','2021-01-23 12:25');
INSERT INTO  tblxyz VALUES (29,1605,  3,'','2021-01-24 10:21');
INSERT INTO  tblxyz VALUES (29,1605,  4,'anothertext','2021-01-24 11:03');
INSERT INTO  tblxyz VALUES (29,324  , 3,'','2021-01-26 06:43');
INSERT INTO  tblxyz VALUES (29,NULL,  2,'sometext','2021-01-26 12:43');
INSERT INTO  tblxyz VALUES (29,1564,  3,'','2021-01-26 16:09');

Query #1

SELECT t1.orderID,t1.SupplierID,t1.Status,t1.Reason,t1.PreviewCreatedAt
FROM (
  select *,
         LAG(Status) OVER(PARTITION  BY orderID ORDER BY CreatedAt) PreviewStatus,
         LAG(Reason) OVER(PARTITION  BY orderID ORDER BY CreatedAt) PreviewReason,
         LAG(CreatedAt) OVER(PARTITION  BY orderID ORDER BY CreatedAt) PreviewCreatedAt
  from tblxyz 
) t1
WHERE PreviewStatus = 2 AND Status = 3 AND PreviewReason='sometext';
orderID SupplierID Status Reason PreviewCreatedAt
29 1605 3 2021-01-23 12:25:00
29 1564 3 2021-01-26 12:43:00

View on DB Fiddle

Upvotes: 1

Related Questions