SQL Server recursive query with left outer join

Question

I have two tables Customers and Orders with some data.

SELECT * FROM  Customers C;

Result:

CustomerId  Name
--------------------    
1           Shree;
2           Kalpana;
3           Basavaraj;

Query:

select * from Orders O;

Result:

OrderId   CustomerId    OrderDate
-------------------------------------------------
100           1         2017-01-05 23:16:15.497
200           4         2017-01-06 23:16:15.497
300           3         2017-01-07 23:16:15.497

I have a business requirement where i need to populate data from Customers left outer join Orders in repeated way. I have written below query and desired data.

SELECT * 
FROM Customers C
LEFT OUTER JOIN 
    (SELECT * 
     FROM Orders 
     WHERE OrderId = 100) O ON O.CustomerId = C.CustomerId

UNION ALL

SELECT * 
FROM Customers C
LEFT OUTER JOIN 
    (SELECT * 
     FROM Orders 
     WHERE OrderId = 200) O ON O.CustomerId = C.CustomerId

UNION ALL

SELECT * 
FROM Customers C
LEFT OUTER JOIN 
    (SELECT * 
     FROM Orders 
     WHERE OrderId = 300) O ON O.CustomerId = C.CustomerId;

Desired Result:

CustomerId  Name       OrderId  CustomerId  OrderDate
--------------------------------------------------------------------
1           Shree      100      1           2017-01-05 23:16:15.497
2           Kalpana    NULL     NULL        NULL
3           Basavaraj  NULL     NULL        NULL
1           Shree      NULL     NULL        NULL
2           Kalpana    NULL     NULL        NULL
3           Basavaraj  NULL     NULL        NULL
1           Shree      NULL     NULL        NULL
2           Kalpana    NULL     NULL        NULL
3           Basavaraj  300      3           2017-01-07 23:16:15.497

I have one option to put left outer query in loop and pass the OrderId and finally save the result data but that takes lots of time because of high number of records. I want to know the best way to get this done. I have tried function and CTE but no luck so far. Please help.

Many thanks in advance.

Answer 1

A cartesian product can do the job:

SELECT  C.*, 
        OrderId = CASE WHEN C.CustomerId = O.CustomerID THEN O.OrderId ELSE NULL END,
        CustomerId = CASE WHEN C.CustomerId = O.CustomerID THEN O.CustomerId ELSE NULL END,
        OrderDate = CASE WHEN C.CustomerId = O.CustomerID THEN O.OrderDate ELSE NULL END
FROM Orders O, Customers C 

Answer 2

I have got the solution using similar to Cartesian product. Store the CustomerId in table variable and than make Cartesian production with same. This works as i wanted.

declare @CustomerTable TABLE (ID int IDENTITY(1,1) NOT NULL, CustomerId int); insert into @CustomerTable select distinct CustomerId from orders;

select v.ID,isnull(v.CT_CustomerId,o.CustomerId) as CT_CustomerId,v.CustomerId,v.Name,o.* from (select CT.ID,CT.CustomerId as CT_CustomerId,C.CustomerId,C.Name from @CustomerTable CT,Customers C ) V left outer join Orders O ON O.CustomerId = V.CustomerId and V.ID=o.ID