Multiple SELECTS with same condition

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
 T2.pos_1 = ***  and pos_2 = ***  and pos_3 = ***

and >= - 10
and <= + 10
and != )


 select *
from listado T3
 where pos_1 = ***  and pos_2 = ***  and pos_3 = ***    and exists (
   select id
   from listado T4
 T4.pos_1 = ***  and pos_2 = ***  and pos_3 = ***

and >= - 10
and <= + 10
and != )


 select *
from listado T5
 where pos_1 = ***  and pos_2 = ***  and pos_3 = ***    and exists (
   select id
   from listado T6
 T6.pos_1 = ***  and pos_2 = ***  and pos_3 = ***

and >= - 10
and <= + 10
and != )


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

Confusing. Please provide sample ddl and dml

1 Like

Hello Yosiasz, thank you for your attention. The example at the end is simply that the result of each of these 3 selects is not separated and independent among them, but that the results of the 3 are intermingled as if it were a single broad selection.
And with the same condition that the 3 share that the results do not have more than 10 id positions between them.

If I ask you to do the search for the rows:
1st SELECT: 0 - 1 - 4
2nd SELECT: 2 - 4 - 8
3rd SELECT: 3 - 5 - 6

What you will do right now, as the query is done, will show me this:

82 0 1 4
83 0 1 4
86 0 1 4

On the other hand:

16 2 4 8
20 2 4 8
24 2 4 8

And on the other:

256 3 5 6
260 3 5 6
262 3 5 6

If you look at the results, apparently they are correct because they are showing me what I have asked for and also the results are grouped by an id that is not more than 10 positions apart.
But, this omitting all the results that are mixed and so appear in the table as for example would be:

82 0 1 4
84 3 5 6
86 0 1 4
89 3 5 6
--- --- --- ---
90 3 5 6
91 2 4 8

This last result would be correct since it would be located in the table, the lines are as they are supposed to appear in the list of data and only shows me that group of rows BECAUSE IT IS THE ONLY ONE OF ALL THE POSSIBLE THAT IS NEARLY NEXT GROUPED BY ID = 10.

Please provide sample data in the following form

Create table #sample(I'd int, column1 int, column2 int)

Insert into #sample
Select 82, 0,1,4 union
Select 84, 3, 5 ,6 union


And then the final result you want to see.

It is just as I am showing it to you, and the data is all INT, there is no more depth in it.
As I just put it in this last comment is how it is.
At the beginning of the post I have placed the code as I have it and in the second comment an example of what is and what should be.

Most helpers in here have great experience working with MSSQL (and I'll bet most actual make a living working with MSSQL), and we're all offering our help/expertice for free. To help people, we often build test environment in order to come up with the best solution. We can't build this environment, loading pictures or handcrafted tables, so we have to enter the data by hand. What we're asking is, that you provide us the means to build this environment quick and easy (it's only fair, as you already have the data and it's you that want help), so we can spend our time on solving your problem. In other words - help us, helping you.

Now; @yosiasz has already asked you twice, and I doubt he'll ask again.

I'm sorry if the above was written to harsh, but I (for one) don't have time punching in data manually.

If you decide to accommodate the request from @yosiasz please also reveal which version of MSSQL the solution should work on.

A bitmed greeting, everything you said is correct and I have nothing to say basically because I totally agree with it. It happens that I do not believe that this post I have made exposing my doubt this lack of data and information ... is more, I have provided all the data that I have and I have tried to do as accurately and clearly as possible and I have even done an effort to present the information as understandable as possible to help the reader. If we observe the beginning of the post with all the code placed and explained added to the second comment with graphical tables of what happens to me and what I need to happen to me, I can not understand what else this post needs to be able to help those who try to help me to solve the question as rightly you indicate me.
In any case if this is not enough, as I do not have any more data and with this information you can not reach any hypothesis as it seems nothing happens, thank the trouble of entering to read my problem and greetings.

Have a look at this:
Especially step 2 and 3

Caballero, por favor damanos sample data. No podemos ayudarte con fotos y otras cosas. Necessitamos data real

The UNION operator uniques the rows returned. Are you looking for UNION ALL?