What happens if you reorder the tables:

FROM 
	holding thold 
	left outer join holding yhold on (thold.portid = yhold.portid and thold.securityid = yhold.securityid) 
	inner join security on thold.securityid = security.securityid 
	inner join portfolio on thold.portid = portfolio.portid 
WHERE 
	thold.date = @asofdate 
	and yhold.date = @prevdate

You'll still have to make Sergey's first suggestion and allow a NULL value for yhold.date.

-Mike

>Hi,
>
>I'm trying to join a table onto itself based on a join of two fields (see below the FROM and WHERE clauses). The third join "left join holding yhold..." is joining the holding table onto the holding table based on two fields the portid and securityid. The result I am receiving is only the matching records even though I have specified left join. What I want is all the records from thold and any matching from yhold.
>
>Is what I am attempting to do permissable? If so any suggestions would be appreciate.
>
>
>FROM holding thold join security on thold.securityid = security.securityid
> join portfolio on thold.portid = portfolio.portid
> left join holding yhold on (thold.portid = yhold.portid and thold.securityid = yhold.securityid)
>WHERE thold.date = @asofdate and yhold.date = @prevdate
>
>Thanks