CODEWITHSUNDEEP
sqlsql-serverjoin
user3514896
user3514896

Reputation: 3

How to implement left outer join?

I have this query i want to implement left outer join how can i do it

SELECT A.cl_id, A.cl_name, A.number_of_orders, B.Dispatch
FROM (
    SELECT CL_Clients.cl_id, CL_Clients.cl_name,COUNT(*) AS number_of_orders
    FROM CL_Clients,CLOI_ClientOrderItems
    WHERE CL_Clients.cl_id = CLOI_ClientOrderItems.cl_id
    GROUP BY CL_Clients.cl_name,CL_Clients.cl_id
    ) A
, (
    SELECT CL_Clients.cl_id,count(*) AS Dispatch
    FROM CLOI_ClientOrderItems,CL_Clients
    WHERE cloi_current_status = '12'  
        AND CL_Clients.cl_id = CLOI_ClientOrderItems.cl_id
    GROUP BY CL_Clients.cl_name,CL_Clients.cl_id, CLOI_ClientOrderItems.cloi_current_status
    ) B

WHERE A.cl_id = B.cl_id

expected output

Cl_id    | Cl_name       |Builtorder |Dispatch
------------------------------------------------
admin    |admin 1    |0      |20
agar     |Agar Murdoch   |102        |1 
Alexander|Alexander James|33         |65    
alexandra|Alexandra West |64         |14    
Archer   |Archer & Co    |0      |5
asbank   |Stirling       |80         |8 
asnapebr |Andrew Snape   |183        |1 
asnapech |Andrew Snape Ch|213        |0

Upvotes: 0

Views: 108

Answers (2)

AK47
AK47

Reputation: 3797

I think your full query can be written in very few line,Please try below code.

First Way, Single Join that is ALL !!!

Select a.cl_id
        ,a.cl_name
        ,count(b.cl_id) as number_of_orders
        ,Sum(Case When b.cloi_current_status ='12' 
                           --and Condition1 and condition2
                     Then 1 else 0 end) as Dispatch
    from CL_Clients as a
    left join CLOI_ClientOrderItems as b on a.cl_id = b.cl_id
 group by a.cl_id
        ,a.cl_name

Second way, multiple joins

Select a.cl_id
        ,a.cl_name
        ,Isnull(orderTable.orders,0) as number_of_orders
        ,ISNULL(dTable.Dispatch,0) as Dispatch
    from CL_Clients as a
    outer apply 
        (
            select b.cl_id,count(b.cl_id) as orders from  CLOI_ClientOrderItems as b 
            where a.cl_id = b.cl_id 
            group by b.cl_id
        )as orderTable 
    outer apply
        (
            select d.cl_id,count(*) as Dispatch from CLOI_ClientOrderItems as d
            where a.cl_id = d.cl_id
                and d.cloi_current_status ='12' 
                --and condition2
                --and condition3
                --..............
                --and condition250
            group by a.cl_id
        )as dTable

In above statement, First join as orderTable is used to get number of orders. Second join as dTableis used to get number of orders which are dispatched, it can contain multiple Conditions as you have asked for, -----Condition2 ..... is dummy text you can remove all comments like Condition2,Condition3 ... & put your actual conditions.

Upvotes: 3

David
David

Reputation: 34543

This is how you would do it in TSQL, but @AK47 has the solution that solves your bigger problem.

SELECT A.cl_id, A.cl_name, A.number_of_orders, B.Dispatch
FROM (
    SELECT CL_Clients.cl_id, CL_Clients.cl_name,COUNT(*) AS number_of_orders
    FROM CL_Clients,CLOI_ClientOrderItems
    WHERE CL_Clients.cl_id = CLOI_ClientOrderItems.cl_id
    GROUP BY CL_Clients.cl_name,CL_Clients.cl_id
    ) A
LEFT OUTER JOIN (
    SELECT CL_Clients.cl_id,count(*) AS Dispatch
    FROM CLOI_ClientOrderItems,CL_Clients
    WHERE cloi_current_status = '12'  
        AND CL_Clients.cl_id = CLOI_ClientOrderItems.cl_id
    GROUP BY CL_Clients.cl_name,CL_Clients.cl_id, CLOI_ClientOrderItems.cloi_current_status
    ) B
ON A.cl_id = B.cl_id

Upvotes: 1

Related Questions