CODEWITHSUNDEEP
sqlsql-server-2008joinunion
Waleed ELerksosy
Waleed ELerksosy

Reputation: 51

Union with join in SQL Server

I have my SQL code that is doing the following: it gets all the "Text" where (O_Top - previous O_Top) < 30 and (O_Left - previous O_Left) < 100

The data in the table:

Text      O_Top        O_Left     
------------------------------
 D         536          654
 5         401          544
 1         392          581
 2         395          609
 K         418          666
 1         329          142
 K         421          711
 H         424          753
 9         228          1194
 5         205          666

The SQL code:

;WITH cte AS
(
    SELECT 
        [Text], [O_Top], [O_Left], 
        ID = ROW_NUMBER() OVER (ORDER BY [O_Left]) 
    FROM
        MyTableName
)
SELECT * 
FROM cte AS c1
LEFT JOIN CTE c2 ON c1.ID = C2.ID - 1
WHERE ABS(c1.O_Top - c2.O_Top) < 30 
  AND ABS(C1.O_Left - c2.O_Left) < 110
ORDER BY C1.[O_Left]

The result I get:

Text    O_Top   O_Left  ID      Text    O_Top   O_Left  ID
-----------------------------------------------------------
5        401    544     2        1       392    581     3
1        392    581     3        2       395    609     4
K        418    666     7        K       421    711     8
K        421    711     8        H       424    753     9

I'm looking up to do like UNION to have the result as follows:

Text    O_Top   O_Left  ID
---------------------------
5        401    544     2        
1        392    581     3        
K        418    666     7        
K        421    711     8        
1        392    581     3
2        395    609     4
K        421    711     8
H        424    753     9

I'm searching a way to avoid the repetition of the columns and will be really amazing if I can use something like distinct in same SQL command so will show me unique results as now is repeating ID's like 8 and 3

I try to think a lot about but I feel the command is that much complex that my brain can't handle it

Thanks a lot in advance for help and support!

Upvotes: 1

Views: 62

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I don't see the relationship between your data and your query. But, if you want to unpivot your results, you can use cross apply:

With cte as (
      Select [Text],[ O_Top], [O_Left],
             ID = Row_Number() over(order bY [O_Left]) 
      from MyTableName
     )
Select v.*
from cte c1 left join
     cte c2
     on c1.ID = C2.ID - 1 cross apply
     (values (c1.[Text], c1.O_Top, c1.O_Left, c1.id),
             (c2.[Text], c2.O_Top, c2.O_Left, c2.id)
     ) v([Text], O_Top, O_Left, id)
where ABS(c1.O_Top - c2.O_Top) < 30 and
      ABS(C1.O_Left - c2.O_Left) < 110;

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

In your query you can just add OR c1.ID = C2.ID +1 on a self join condition between the cte, and select just from c1. This way you would be comparing both previous and next row at the same time. Add distinct if you wish unique results.

;With cte as
  (Select [Text],[O_Top],[O_Left], ID = Row_Number() over(order bY [O_Left]) 
    from MyTableName)

 Select distinct c1.* 
 from cte as c1
 left Join cte  c2
 on c1.ID = C2.ID - 1 OR c1.ID = C2.ID +1
 where ABS(c1.O_Top - c2.O_Top) < 30 
 and ABS(C1.O_Left - c2.O_Left) < 110
 order bY C1.[O_Left]

or replace two OR conditions with ABS(c1.ID - C2.ID) = 1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

You can achieve what you want with a union query of your current CTE. Try this, and see below for an explanation:

WITH cte2 AS (
    SELECT
        c1.Text AS Text1, c1.O_Top AS O_Top1, c1.O_Left AS O_Left1, c1.ID AS ID1,
        c2.Text AS Text2, c2.O_Top AS O_Top2, c2.O_Left AS O_Left2, c2.ID AS ID2
    FROM cte AS c1
    LEFT JOIN cte c2
        ON c1.ID = c2.ID - 1
    WHERE ABS(c1.O_Top - c2.O_Top)   < 30  AND 
          ABS(c1.O_Left - c2.O_Left) < 110
),
cte3 AS (
    SELECT
        Text1 AS Text, O_Top1 AS O_Top, O_Left1 AS O_Left, ID1 AS ID, 1 AS pos
    FROM cte2
    UNION ALL
    SELECT
        Text2, O_Top2, O_Left2, ID2, 2
    FROM cte2
)

SELECT *
FROM cte3
ORDER BY
    pos,
    O_Left

To make this work, I made the following changes:

  • Used explicit aliases for the two sets of columns in your original CTE
  • Added a computed column in the cte3 union query to keep track of which set of values are being selected
  • Selected from cte3 using the computed column above to order the records the way you want

Upvotes: 1

Related Questions