SQL Left Outer Join question 1

[dbrs4me]

I started by needing a query that pulled all the orders created for a given time frame and then needing to see what orders have been sent during that same time period. There are times when the order has been created, but not sent. Here is the table structure:

ORDER
ID
ORDERNUM
DATE_CREATED
CUSTOMER_ID

101|11111|5-DEC-15|10000
102|11112|6-DEC-15|10010
103|11113|7-DEC-15|10020

SENT
ID
ORDER_ID
CONTACT_ID

901|101|200
902|103|202

CUSTOMER
ID
NAME
NUMBER

10000|COMP1|45645
10010|COMP2|12321
10020|COMP3|78987

I figured a left outer join would do the trick. Here's the SQL, and it worked at first.

Select O.ORDERNUM "SON, S.ORDER_ID "Ord_ID", C.COMPANY "Comp", C.NUMBER "CoNo"
from orders o left outer join sent s on o.id = s.order_id, customer c
where o.date_created > (sysdate -10)
and o.customer_id = c.id
order by o.id;


SON Ord_ID Comp CoNo
----------------------------------------------------------
| 11111 | 101 | COMP1 | 45654 |
----------------------------------------------------------
| 11113 | 103 | COMP3 | 78987 |
----------------------------------------------------------
| 11112 | (null) | COMP2 | 12321 |
----------------------------------------------------------

Now, I have been asked to add the email address, that is in a fourth table:

CONTACT
ID
EMAIL

200|bob@comp1.com
201|tim@comp2.com
202|mel@comp3.com


When I add that to the script, in the way I think it should go, I only get those records that have an ORDER_ID in the SENT table

Select O.ORDERNUM "SON", S.ORDER_ID "Ord_ID", C.COMPANY "Comp", C.NUMBER "CoNo", CO.EMAIL "Email"
from orders o left outer join sent s on o.id = s.order_id, customer c, contact co
where o.date_created > (sysdate -10)
and o.customer_id = c.id
and s.contact_id = co.id
order by o.id;


SON Ord_ID Comp CoNo Email
--------------------------------------------------------------------------
| 11111 | 101 | COMP1 | 45654 | bob@comp1.com |
--------------------------------------------------------------------------
| 11113 | 103 | COMP3 | 78987 | mel@comp3.com |
--------------------------------------------------------------------------


Any ideas on what I am missing? I feel like I should still get the null row, but it's gone.
(I apologize on the formatting, I haven't posted in years)

Thanks

 

[fredericofonseca]

argh!!!!.. do not mix old and new styles of joins... EVER.

what you have there is 3 inner joins and 1 left outer join - and on top of that your where clause which requires you to have records from Contact also mean that only records from sent are retrieved which nullifies the left outer join you applied.

you probably wished for

Select O.ORDERNUM "SON"
     , S.ORDER_ID "Ord_ID"
     , C.COMPANY "Comp"
     , C.NUMBER "CoNo"
     , CO.EMAIL "Email"
from orders o 
left outer join customer c -- or inner join if that is applicable as it does not affect the other 2 joins
   on o.customer_id = c.id
left outer join sent s 
   on o.id = s.order_id
left outer join contact co
   on s.contact_id = co.id
where o.date_created > (sysdate - 10)
order by o.id;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886