INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Left Outer Join question

SQL Left Outer Join question

(OP)
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

RE: SQL Left Outer Join question

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

CODE

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close