Complicated SQL Join

Question

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

Normally I would just join where the record is for that Customer 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 join on three rows from the lookup table. The ID that matches, and the row before and after (if they exist) based on the the sort order in another column (Order). I then need to sort the result, with the record that matches first, then the lookup record that was before, then the lookup 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

Answer 1

Not the most efficient, or elegant, but it works!

Data setup:

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

The Query:

;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)