outer join operation?

Hello everyone:

I am doing a sql exercise which states like this, "Determine which orders have not yet shipped and the name of the customer that placed each order. Sort the results by the date on which the order was placed."

this exercise asks me to use traditional approach(using where clause along with outer join operator(+), and using the join keyword)

the tables used for this exercise is customers and orders. the data type of each table are as follows:

customers table: contaion all the customers who placed orders and who have not recently placed an order

Name Null? Type
------------- --- -------
CUSTOMER# NOT NULL NUMBER(4)
LASTNAME VARCHAR2(10)
FIRSTNAME VARCHAR2(10)
ADDRESS VARCHAR2(20)
CITY VARCHAR2(12)
STATE VARCHAR2(2)
ZIP VARCHAR2(5)
REFERRED NUMBER(4)

order table: this table contains data for current month orders or unfilled orders from previous month.

Name Null? Type
------------- --- -----
ORDER# NOT NULL NUMBER(4)
CUSTOMER# NUMBER(4)
ORDERDATE DATE
SHIPDATE DATE
SHIPSTREET VARCHAR2(18)
SHIPCITY VARCHAR2(15)
SHIPSTATE VARCHAR2(2)
SHIPZIP VARCHAR2(5)

the two tables have customer# attribute in common. I think I should use outer join for this exercise. I did several tries. i guess i am still little confused about the defination for ourter join when it comes to using 'join' keyword along with left, right full option.

I did three attempts but each one give me different results. I couldn't figure out which one is right. Please help.

--My own selection statement are as follows:
a) traditional approache (using where clause)

select order#, lastname, firstname, shipdate
from customers c, orders o
where c.customer# = o.customer#(+)
order by o.shipdate

27 rows were selected

my thinking on this is if there is no corresponding row in customers table, which means that customer who have not been place an order recently. the results will display customer who have not been place an order recently. what if I put (+) on left side will this give me the correct result?

b) use join keyword.

select lastname, firstname, order#, shipdate
from customers c right join orders o
on c.customer# = o.customer#
order by o.shipdate

21 rows were selected.

Please give me some suggestion on how to solve this problem. if you know some good web source that give clear explnation on outer join along with examples, pleate refer them to me. I will appreciate your help!

Sincerely,
sjgrad03
1-25-05if the assignment actually requires that you use an OUTER JOIN, the first thing you need to do is decide which of the tables will have a "missing" row

in your second example, customers c right join orders o, you are saying that there can be orders without matching customers

i think that's wrong

what you want is customers c left join orders o

this will return all customers, including those who have no matching orders

it would be extremely unlikely to have an order without a matching customer

the second thing you need to focus on is the actual requirement -- "orders which have not yet shipped"

so what you want is orders

therefore, an OUTER JOIN here would be wrong!!

you don't want customers without orders, you want orders and their customers

(assuming, again, that you cannot have an order without a customer)

if this course or assignment specifically said you must use an OUTER JOIN, then it stinks!

in addition, there is no reason why they should be teaching you a proprietary method like (+), unless the purpose of the course or assignment is to teach you how to move away from that syntax and start to learn the standard

Outer Join Issues - Please Help

I am working on the record source for a report to produce monthly customer statements. Thanks to everyone here, I have been able to overcome many, many hurdles I have encountered. I just have one last issue I need to get resolved (famous last words, I know).

At the heart of my record source are two entities:

1. AROPNFIL - A table of all Accounts Receivable items (invoices, payments, credits, etc.)
2. fnBalance(@.StartDate) - A table valued function that gives me the starting balance for a customer on a particular date by summing the net Amounts of all items in the AROPNFIL that occured before the @.startdate. (i.e. Invoices have a positive amount, payments have a negative amount, so they net out so that only unpaid invoices remain.)

These are joined on customer_number. The query also selects only the rows in the AROPNFIL table that are between @.StartDate and @.EndDate for the month.

Everything was going great until...I realized that no statement was being created for a customer if they didn't have any activity in the current month, even though they had a starting balance.

So I tried an outer join, telling the query to select all rows from the fnBalance table. But that still wasn't returning the rows I wanted. After several hours of cursing and feeling the need for a drink, I realized why that wasn't working. Because the customer in question, let's call it "Coast01" had rows in the AROPNFIL table before the @.StartDate, it was seeing that as having completed the join and hence no reason to return a row for that starting balance.

So, this is what I need, a way to write what I am going to try to interpret as the following.

Select *
From (AROPNFIL WHERE Doc_date BETWEEN @.StartDate AND @.EndDate) RIGHT OUTER JOIN fnBalance(@.StartDate)

Does that make sense? I need the join with fnBalance to take place after the query has selected only the rows between @.startdate and @.enddate so that the row for the balance for "Coast01" will appear even though there is no activity in the current month.

But I am at a loss for how to write that in the FROM and WHERE clauses. I guess one way would be to create a query selecting rows from AROPNFIL WHERE doc_date BETWEEN @.StartDate AND @.EndDate and then create another query performing the outer join between that query and fnBalance, but I don't really want to do that because the rest of my record source query is actually a lot more complicated than what I've explained here, and I'd rather not have to create additional queries if I don't have to. But if you guys tell me there is no other way, I'll believe you.

Thank you all!Actually, I think I'm on the right path here using a derived table, please correct me if I'm wrong:

SELECT *
FROM (SELECT * FROM
AROPNFIL WHERE Doc_date BETWEEN @.StartDate AND @.EndDate) RIGHT OUTER JOIN
fnBalance(@.StartDate) StartBalance ON AROPNFIL.cus_no = StartBalance.cus_no

Obviously, I'll get rid of the *s, but no one here cares about what columns I'm actually pulling out.|||This is correct, I got it to work!!!