I think

yhold.date = @prevdate

effectively eliminates all records with no match in yhold table. Try

... (yhold.date = @prevdate OR yhold.date IS NULL)

>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