Geek Answers Handbook

Sophisticated SQL Join

I am trying to join two tables together, but have an odd requirement.

Usually I just join the record for this Client, and the code matches

SELECT * 
    FROM DataTable d
    JOIN LookupTable l
        ON d.LookupCode = l.LookupCode
        AND d.Customer = l.Customer

However, what I need to do is combine the three rows from the lookup table. The identifier that matches, and the line before and after (if they exist) based on the sort order in another column (Order). Then I need to sort the result with the record that matches the first, and then the search record that was before, then the search record that was after the matched record.

Any thoughts on the best way to accomplish this?

Example:

Lookup:
Customer Code   Order
12345    A      1
12345    B      2
12345    C      3
12345    D      4
12345    E      5
22222    A      1
22222    B      2
22222    D      4
22222    E      5

Data:
Customer    Code
12345       B
12345       D
22222       B
22222       D

Result I need
Customer    Code
12345       B
12345       A
12345       C
12345       D
12345       C
12345       E
22222       B
22222       A
22222       D
22222       D
22222       B
22222       E
+5
source share
2 answers

Not the most efficient or elegant, but it works!

Data setting:

CREATE TABLE LookupTable (Customer int, Code nvarchar(1), OrderCol int)
CREATE TABLE DataTable (Customer int, Code nvarchar(1))

insert LookupTable  values (12345,'A',1),(12345,'B',2),(12345,'C',3),(12345,'D',4),(12345,'E',5),(22222,'A',1),(22222,'B',2),(22222,'D',4),(22222,'E',5)
insert DataTable values (12345,'B'),(12345,'D'),(22222,'B'),(22222,'D')

select * from LookupTable 
select * from DataTable

Inquiry:

;with LookupCte as (
    SELECT  *    
        ,   ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY OrderCol ASC) AS LookUpOrder
    FROM    LookupTable
)
, DataCTE as (
    SELECT  dt.Customer
        ,   dt.Code
        ,   lu.LookUpOrder
        ,   (lu.LookUpOrder - 1) AS OrderColNVe
        ,   (lu.LookUpOrder + 1) AS OrderColPVe
        ,   ROW_NUMBER() OVER (PARTITION BY dt.Customer ORDER BY dt.Code ASC) AS DataCteRowNumber
    FROM    DataTable dt
    INNER JOIN LookupCte lu
        ON lu.Customer = dt.Customer
        AND lu.Code = dt.Code

)
, UnionCTE As (

    SELECT  d.Customer
        ,   d.Code
        ,   d.DataCteRowNumber
        ,   1 AS [CustomOrder]
    FROM  DataCTE d

    UNION ALL 

    SELECT  lt.Customer
        ,   lt.Code
        ,   d.DataCteRowNumber
        ,   2 AS [CustomOrder]
    FROM    DataCTE d 
    INNER JOIN LookupCte lt on lt.Customer = d.Customer
        AND lt.LookUpOrder = d.OrderColNVe

    UNION ALL

    SELECT  lt.Customer
        ,   lt.Code
        ,   d.DataCteRowNumber
        ,   3 AS [CustomOrder]
    FROM    DataCTE d 
    INNER JOIN LookupCte lt on lt.Customer = d.Customer
        AND lt.LookUpOrder = d.OrderColPVe
)
SELECT  u.Customer
    ,   u.Code
FROM    UnionCTE u
ORDER BY u.Customer, u.DataCteRowNumber, u.CustomOrder

gives:

Customer    Code
----------- ----
12345       B
12345       A
12345       C
12345       D
12345       C
12345       E
22222       B
22222       A
22222       D
22222       D
22222       B
22222       E

(12 row(s) affected)
+2

: , row_Number()

;with c as (
    Select d.Customer
       , d.code
       , l.order
    from Data as d
    inner join Lookup as l
    on d.customer = l.Customer
       and d.Code = l.Code
    )
    select
        c.Customer
          ,  C.Code 
    from c
    union all
    Select 
      , c.Customer
      , l.code
    from c
    inner join lookup as l
    on c.Customer = l.Customer
      and c.Order = l.Order + 1
    Select 
      , c.Customer
      , l.code
    from c
    inner join lookup as l
    on c.Customer = l.Customer
      and c.Order = l.Order - 1
0

More articles:


All Articles