Outer-join w/ a subselect?

I am trying to join two tables, return all data from one table
(m)and the matching records from the other (sa). So far, a
straight outer join.

A data field in table A needs to match a field in table B. There could be multiple A records that meet this restriction. I need to choose the most recent record.

I have written some SQL that returns only the records that match in both tables. It appears that the subselect is over-riding the outjoin (+). Is there a fix for this. Please see code below.

SELECT	m.year_id,
	sa.sale_id,
	sa.sale_date,
	sa.sale_price
FROM	ma_master m,
	ma_sales sa
WHERE   m.p_id = sa.p_id (+)
AND	sa.sale_id = (
	SELECT	MAX(sale_id)
	FROM	ma_sales
	WHERE	p_id = sa.p_id
	AND	sale_date <= TO_DATE('09/30/' || TO_CHAR


(m.year_id-1),'MM/DD/YYYY')

	AND	sale_date >= TO_DATE('10/01/' || TO_CHAR


(m.year_id-2),'MM/DD/YYYY')

	)
AND	m.year_id = 2000

Craig Buchanan
Cogniza, Business Intelligence Specialists www.cogniza.com
+1.612.824.5858

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Wed Jul 12 2000 - 00:00:00 CDT