r0xette r0xette - 3 months ago 6
MySQL Question

comparing rows in the same table

id Status Material Week
-------------------------------------
a1 Clear iron 33
a1 Clear iron 34
a1 Shipped iron 35
b1 Cancel Brass 33
b1 Cancel Brass 34
b1 Cancel Brass 35
c1 Identify Plastic 34
c1 Clear Plastic 35


Hello, I am trying to come up with a query where I can pick all those IDs whose Status or Material have changed between week 34 and week 35. If anything changed between week 33 and 34 or 33 and 35, ignore it. Also if anything which is not present in week 33 or 34 but present in week 35, it has to be included. At the end the ID should be unique.

So far I have come up with the following query.

SELECT DISTINCT t1.id
FROM table t1
JOIN table t2
ON t1.id = t2.id
WHERE t1.Status<>t2.Status or t1.Material<>t2.Material


Expected result:

id
a1
c1

Answer

How about this?

SELECT 
 T1.id
FROM t T1
LEFT JOIN t T2 ON T1.id = T2.id  
AND T2.Week = 34 
WHERE T1.Week = 35
AND IF(T2.id IS NULL, TRUE, ((T1.Status <> T2.status) OR (T1.Material <> T2.Material)))

Demo here


Just take two instances T1 (row for week #35) and T2 (row for week #34)

T2.week = 34

T1.week = 35

And if row of week #34 exists then either the status or the material of T1 and T2 must differ in order to appear in the output. Refer to the IF statement in the above query


Test:

create table t(
  id varchar(10),
  Status varchar(10),
  Material varchar(10),
  week int
  );

INSERT INTO t(id,status,material,week)
VALUES('a1','clear','iron',33),
('a1','clear','iron',34),
('a1','shipped','iron',35),
('b1','cancel','brass',33),
('b1','cancel','brass',34),
('b1','cancel','brass',35),
('c1','identify','plastic',34),
('c1','clear','plastic',35);

Output:

id
a1
c1
Comments