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