First Question. I am using SQL Server 2012. I couldn’t find a similar problem in other places, and I am still learning SQL.
I am trying to get a subquery to return one value. My table (JobMilestones) is unique when you compare JobPaperID AND Milestone. Any independent query will return only one value or no values. It gives and error –

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

The rest of the query is giving the right results when I don’t add the subquery, however I do need the subquery as well. In the table JobMilestones I’ll either have an entry for a specific job which has a unique JobPaperID AND a unique Milestone (taken together), or I’ll have no entry (I am suspecting the latter is the problem). I expect the subquery to return CompletedDate or to return a single NULL value if the record doesn’t exist. I expect it to return a single NULL (where there are no records) or the value of CompletedDate. Where am I going wrong?

This is the code I am using:

SELECT 
    mjd.ID as JobID,
    mjd.JobNo as JobNo, 
    mjd.CreateDateTime as JobCreatedDateTime, 
    mjd.InvoiceCustomerCode as CustomerCode, 
    mjd.MilestoneGroup as MilestoneGroup, 
    mg.Name as MilestoneGroupDescr, 
    ml.MilestoneID as MilestoneID, 
    case m.CustomType
        when 0 then m.Type
        else m.CustomType
    end AS Milestone, 
    m.Name as MilestoneName,
    jp.ID as JobPaperID, 
    jp.Name as JobPaperName,
    (select jms.CompletedDate
        from dbo.JobMilestones AS jms
        where ((jms.JobPaperID = jp.ID) 
            AND (jms.Milestone = Milestone))
    ) AS CompletedDate
FROM dbo.MainJobDetails AS mjd
INNER JOIN dbo.MilestoneGroup AS mg
    ON mjd.MilestoneGroup = mg.Code
INNER JOIN dbo.JobPaper AS jp 
    ON mjd.ID = jp.JobID
INNER JOIN dbo.MilestoneLink AS ml 
    ON mg.ID = ml.MilestoneGroupID
INNER JOIN dbo.Milestones AS m 
    ON ml.MilestoneID = m.ID
WHERE mjd.ID = 87101 

If I change it to SELECT TOP 1 all I get is NULL and none of the valid entries