SQL View which returns value or null if not found

Question

I don't think this can be done but as I am a bit of a rookie using MSSM studio for a SQL database I thought I'd ask anyway.

I have 2 tables and use a view to see matching records. Table 2 sometimes doesn't have matching records so naturally they won't show in my view. For example:

A-1
B-2
C-3

What I really need is for the view to show me the contents for the matching records in Table 2 but at the same time show me that nothing was found for the others:

A-1
B-2
C-3
D-"Not Found"
E-"Not Found"

This way I can prepare my view exactly as I need it but can go through the ones that aren't found in table 2. Hopefully someone can help me find a way to get this done. Thanks in advance.

--------EDIT----------

OK so the first table (GoogleBusinessData) holds.amongst other things, clients websites. The second table (EmailTable) holds a number of email addresses. The common link is the BusWebsite column.

SELECT dbo.GoogleBusinessData.BusWebsite, dbo.EmailTable.EmailNumberOfEmails
FROM   dbo.GoogleBusinessData 
INNER JOIN dbo.EmailTable ON dbo.GoogleBusinessData.BusWebsite = dbo.EmailTable.EmailWebsite

The problem here is that it will list the emails that appear when the websites match, but I would love to be able to list all of my data from dbo.GoogleBusinessData.BusWebsite, and if it is matched with EmailTable.EmailWebsite then it would display the number in dbo.EmailTable.EmailNumberOfEmails, but if it's not found then display "Not Found".

Answer 1

Use a LEFT JOIN (also termed a LEFT OUTER JOIN) between the Table1 and Table2

and use

CASE WHEN Table2.Key IS NULL THEN 'Not Found' ELSE Table2.RelatedColumn END

in your SELECT list.

SELECT 
    gbd.BusWebsite, 
    CASE WHEN et.EmailNumberOfEmails IS NULL 
         THEN 'Not Found' 
         ELSE et.EmailNumberOfEmails
    END
    et.EmailNumberOfEmails 
FROM dbo.GoogleBusinessData gbd
LEFT JOIN dbo.EmailTable et ON gbd.BusWebsite = et.EmailWebsite

The result of a left outer join (or simply left join) for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).