nbayly nbayly - 4 months ago 11
MySQL Question

Exists in other Rows on same table with duplicate field

I have a table structure as per:

CREATE TABLE `table1` (
`pk_id` bigint(20) NOT NULL AUTO_INCREMENT,
`field1` varchar(16) NOT NULL,
`field2` char(1) DEFAULT NULL,
`field3_date` datetime DEFAULT NULL,
PRIMARY KEY (`pk_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Where
field1
will have duplicates. I am trying to query for all rows with
A
or
B
in
field2
in a specific month, where there exists on the same table row or rows with the same
field1
but with
Z
for
field2
. I have interpreted this as the following query:

SELECT * FROM table1
WHERE (field2='A' or field2='B') AND
MONTH(field3_date)=MONTH('2016-06-01') AND
EXISTS
(
SELECT field1 FROM table1 AS temp_table
WHERE table1.field2='Z' AND table1.field1=temp_table.field1
)
ORDER BY field3_date DESC;


I am not getting any rows returned though I have confirmed that numerous instances fulfill the conditions. I have also tried without
AND table1.field1=temp_table.field1
in my subquery and receive the same result. Any suggestions or indication on what could correct my query? Warm regards,

EDIT: Sample dummy data and expected output provided here:

https://docs.google.com/spreadsheets/d/1-dSXR_sK71ZSrzRpAsfm0VBEP9EDDKTtcMRXOeclwNs/edit?usp=sharing

2nd EDIT: Adding mock data in usable format:

INSERT INTO Table1
(`pk_id`, `field1`, `field2`, `field3_date`)
VALUES
(10000000000000000001, 'SFCAVLDLYCQQFPHB', 'A', '2016-06-01 00:00:00'),
(10000000000000000002, 'BP3RJZCWQFXS32TS', 'B', '2016-06-02 00:00:00'),
(10000000000000000003, 'PVR2HEA6VFXYVFVV', 'A', '2016-06-03 00:00:00'),
(10000000000000000004, 'G7MDHGYVUUELJZCY', 'B', '2016-06-04 00:00:00'),
(10000000000000000005, 'PVR2HEA6VFXYVFVV', 'Z', '2016-06-05 00:00:00'),
(10000000000000000006, 'NC3KNPPGDH6JTCEW', 'A', '2016-06-06 00:00:00'),
(10000000000000000007, '4TYFQNKSRYEJXABH', 'A', '2016-06-07 00:00:00'),
(10000000000000000008, 'BP3RJZCWQFXS32TS', 'Z', '2016-06-08 00:00:00'),
(10000000000000000009, 'EGUR5CUBYRQZ6286', 'B', '2016-06-09 00:00:00'),
(10000000000000000010, 'SQWKPRYJEJDMZ8Y3', 'B', '2016-06-10 00:00:00')
;

Answer

How about this?

SELECT t2.*
FROM   table1 t1
JOIN   table1 t2
ON     t2.field2 = 'Z' AND
       t2.field1 = t1.field1 AND
       t2.pk_id <> t1.pk_id
WHERE t1.field2 IN ('A', 'B') AND
      MONTH(t1.field3_date)=MONTH('2016-06-01')

t1 is your original table; t2 is your duplicate. The comparison of t1.pk_id and t2.pk_id ensures that t1 does not join to itself. Actually, this comparison is not strictly required (because t2.field2 will be 'Z' and t1.field2 will be 'A' or 'B', but it's a good safeguard if you want to modify your query with different selection criteria later.