CODEWITHSUNDEEP
sqloracle-databaseouter-join
user5252179
user5252179

Reputation: 133

Oracle: How to use left outer join to get all entries from left table and satisfying the condition in Where clause

I have the tables below.

Client:

ID | clientName
--------------
1    A1        
2    A2
3    A3

Order:

OrdID clientID status_cd
------------------------
100  1        DONE
101  1        SENT
102  3       SENT

Status:

status_cd  status_category
DONE        COMPL
SENT        INPROG

I have to write a query to get all the clients and count of order against all of them, whether the client_id exists in Order table or not and has the orders with "COMPL" as status category.

In this case, I am using the query below but it's filtering out the clients which has no orders. I want to get all clients such that the expected result is as below.

Query:

select c.ID, count(distinct o.OrdID) 
from client c, order o, status s
where c.ID=o.client_id(+)
and o.status_cd=s.status_cd where s.status_category='COMPL'
group by c.ID

Expected result:

C.ID   count(distinct o.OrdID)
---------------------------- 
1       1
2       0
3       0

Can someone please help me with this? I know, in this case, left outer join is behaving like inner join when I am using where clause, but is there any other way to achieve the results above?

Upvotes: 0

Views: 1660

Answers (3)

user330315
user330315

Reputation:

This can be dealt with a lot easier when using an explicit join operator:

select c.ID, count(distinct s.status_cd) 
from client c
  left join orders o on o.clientid = c.id
  left join status s on s.status_cd = o.status_cd and s.status_category='COMPL'
group by c.ID;

The above assumes that orders.status_cd is defined as not null

Another option is to move the join between orders and status in a derived table:

select c.ID, count(distinct o.ordid) 
from client c
  left join (
    select o.ordid
    from orders o
      join status s on s.status_cd = o.status_cd
    where s.status_category='COMPL'
  ) o on o.clientid = c.id
group by c.ID;

The above "states" more clearly (at least in my eyes) that only orders within that status category are of interest compared to the first solution

Upvotes: 1

user5683823
user5683823

Reputation:

The second WHERE should be an AND.

Other than that, you need the plus sign, (+), marking left outer join, in the second join condition as well. It is not enough to left-outer-join the first two tables.

Something like

select c.ID, count(distinct o.OrdID) 
from client c, order o, status s
where c.ID=o.client_id(+)
and o.status_cd=s.status_cd(+)    AND   s.status_category='COMPL'
--                         ^^^    ^^^  (not WHERE)
group by c.ID

Of course, it would be much better if you used proper (SQL Standard) join syntax.

Upvotes: 0

Roger Cornejo
Roger Cornejo

Reputation: 1547

As usual, there are lots of ways to express this requirement.

Try ANSI join people will hate me an vote down this answer ;) :

select c.ID, count(distinct o.OrdID) 
from client c, order o, status s
where c.ID = o.client_id(+)
and o.status_cd = s.status_cd 
and s.status_category='COMPL'
group by c.ID
;

or

select c.ID
, nvl((select count(distinct o.OrdID) 
   from order o, status s 
   where c.ID = o.client_id 
     and o.status_cd = s.status_cd 
     and s.status_category='COMPL'
   ), 0) as order_count
from client c
group by c.ID
;

or

with ord as
(select client_id, count(distinct o.OrdID) cnt
   from order o, status s 
   where 1=1
     and o.status_cd = s.status_cd 
     and s.status_category='COMPL'
   group by client_id
)
select c.ID
, nvl((select cnt from ord o where c.ID = o.client_id ), 0) as order_count
from client c
group by c.ID
;

or

...

Upvotes: 0

Related Questions