Outer join difficulty

Can someone please help me with the following join ? I have two tables that I need to join. The select statement for the first part is as follows:-

SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
ORDER BY Sharename

The table that is produced is as follows:-

Rundate Accno Sharename NotAtHome
20031128 260869 PANGBOURNE 123200
20031128 260869 PARAMOUNT 221100
20031128 260869 PRISAIB. 221100

The table I wish to join on, is as follows:-

SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
ORDER BY Sharename

The result is as follows:-

Rundate Accno Sharename NotAtHome
20031103 260869 PANGBOURNE 123200

Note that Paramount and Prisaib are missing from the second table.

I need to create an output table showing all three records with the missing two having 0 under "NotAtHome".

I'd much appreciate any assistance with this..

thanks!(SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
ORDER BY Sharename) q1
LEFT OUTER JOIN
(SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
ORDER BY Sharename) q2
ON q1.AccNo = q2.AccNo;

Assuming AccNo is a key.

The default value for non-matching rows is Null, however if you do indeed want a numerical then,

I am not entirely sure of MSSQL syntax however I believe it's the following for Case statements.

select attributes, (case NotAtHome If Null Then 0 Else NotAtHome END) As "NotAtHome"
from
(
Above query
)|||you would not need ORDER BY in the subqueries, and in fact the query is a bit neater without subqueries

the NotAtHome column is weird, i left it as t1.NotAtHome as alkemac specified, but i think he/she might have meant t2.NotAtHome select t1.Rundate
, t1.Accno
, t1.Sharename
, case when t2.AccNo is null
then 0
else t1.NotAtHome
end as NotAtHome
from dbo.Holdings t1
left outer
join dbo.Holdings t2
on t1.AccNo = t2.AccNo
and t2.Rundate = '20031103'
and t2.Sharename like 'P%'
where t1.Accno = '260869'
and t1.Rundate = '20031128'
and t1.Sharename like 'P%'rudy
http://r937.com/