Good to everyone and thanks in advance for your attention.
My question is the following:
I have a table with approximately 700 data with auto-incremental ID and with these fields:
+------+------+------+------+
| id | pos_1 | pos_2 | pos_3 |
+------+------+------+------+
| 1 | 9 | 2 | 3 |
| 2 | 3 | 2 | 9 |
| 3 | 1 | 4 | 3 |
| 4 | 1 | 2 | 3 |
| 5 | 5 | 1 | 6 |
| 6 | 1 | 2 | 3 |
| 7 | 8 | 2 | 0 |
| ... | ... | .. . | ... |
| etc | etc | etc | etc |
+------+------+------+------+
It happens that I always perform a query that contains several selects and a common condition, all united by "UNION" in this way:
select *
from listado T1
where pos_1 = *** and pos_2 = *** and pos_3 = *** and exists (
select id
from listado T2
where
T2.pos_1 = *** and pos_2 = *** and pos_3 = ***
and T2.id >= T1.id - 10
and T2.id <= T1.id + 10
and T1.id != T2.id )
UNION
select *
from listado T3
where pos_1 = *** and pos_2 = *** and pos_3 = *** and exists (
select id
from listado T4
where
T4.pos_1 = *** and pos_2 = *** and pos_3 = ***
and T4.id >= T3.id - 10
and T4.id <= T3.id + 10
and T3.id != T4.id )
UNION
select *
from listado T5
where pos_1 = *** and pos_2 = *** and pos_3 = *** and exists (
select id
from listado T6
where
T6.pos_1 = *** and pos_2 = *** and pos_3 = ***
and T6.id >= T5.id - 10
and T6.id <= T5.id + 10
and T5.id != T6.id )
ORDER BY id ASC
In such a way that this query returns the rows that match the numbers that I have entered in the corresponding positions (pos_1, pos_2, pos_3) of the table, BUT with the condition that the RESULTS of this search do not DISTANCE from each other more of 10 POSITIONS per ID as you can see what I do with the fragment:
and T***.id >= T***.id - 10
and T***.id <= T***.id + 10
and T***.id != T***.id )
Up to here everything is correct, the problem is that when making this query as it is done the RESULTS that I returned are the SELECTS independently from each other, that is, it only shows me the data I consult individually GROUPED SELECTS of the others ... I mean you are omitting ALL the results that intermingle the rows of the 3 SELECTS if that is the way it should be because it appears in the table. I'm getting the results SEPARATELY and I need the SELECTS to be united as a single SELECT and do not separate me and divide the results into three different SELECTS ... and I have no idea how to accommodate the query to do so ... Sharing the condition that the results do not differ from each other by ID more than 10 positions and jointly FOR EXAMPLE the result line of the SELECT nº1 is within the line of the result of SELECT Nº2 or Nº3 if it should be because it is so In the table...
Thank you
