For the same col3 value in mySQL, how to get all rows if col1 of one row is equal to one value and col2 of the same row or different row is equal to another value?

Question

R	C1	C2	C3	C4
R1	1	one	B	1
R2	1	B	C	2
R3	1	C	D	3
R4	2	one	D	1
R5	3	one	D	1
R6	3	D	X	2

I have a table my_table in mySQL which contains the above row and column details. I need to get all values ​​of c1 where starting c2 value = A and ending c3 value = D.

The rows in the table are sorted by R, so the start c2 value for c1=1 is A , the end c3 value of c1=1 is D

I expected output

C1
1
2

For c1=1 there are 3 lines:

R	C1	C2	C3	C4
R1	1	one	B	1
R2	1	B	C	2
R3	1	C	D	3

You can see here that the first C2 is A and the last C3 is D

This satisfies the condition, so the value of 1 for C1 should be included in the output.

For c1=2 there is 1 row:

R	C1	C2	C3	C4
R4	2	one	D	1

Its C2 is A and C3 is D, so this should be included in the output as well.

For c1=3 there are 2 rows:

R	C1	C2	C3	C4
R5	3	one	D	1
R6	3	D	X	2

The first C2 is A, and the last C3 is X

This does not satisfy the condition, so the C1 value of 3 should not be included in the output. < /p>

I only need the C1 value that satisfies the condition.

Answer 1

Assume that the input selection is ordered, and we consider that the grouped sets of C1 values ​​have the same order, starting with the first row C2 and ending with the last row C3 value, then we can construct this sequence for each value of C1:

C1	MinimumR	C2	MaximumR	C3
1	R1	one	R3	D
2	R4	one	R4	D
3	R5	one	R6	X