SQL, how to return NULL

tanj1035
tanj1035 used Ask the Experts™
on
Hi Expert,

I have 2 tables
1) Archive Reason

ArchivedDealID              Name
0                                      Sold
1                                      Lost
2                                      Test


2) Deal

ArchiveDealStatus
Null
0
1
2

Select d.archivedealstatus, a.name
from Deal d
left join archivereason a on d. archivedealstatus= a. archivedealID

But the query only returns
archivedealstatus 0,1,2, without Null.

I want to see the result . Thank you!

Archivedealstatus   Name
 Null                            null
0                                 sold
1                                 lost
2                                 test
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Contract ERP Admin/Consultant
Commented:
This is a working example using temporary tables, and it shows the NULLS. The only difference is I reversed the ON criteria:

DECLARE @ArchiveReason TABLE (
    ArchivedDealID      tinyint     NOT NULL,
    Name                varchar(8)  NOT NULL )

INSERT INTO @ArchiveReason VALUES (0, 'Sold')
INSERT INTO @ArchiveReason VALUES (1, 'Lost')
INSERT INTO @ArchiveReason VALUES (2, 'Test')

DECLARE @Deal TABLE (ArchiveDealStatus   tinyint)

INSERT INTO @Deal VALUES (NULL)
INSERT INTO @Deal VALUES (0)
INSERT INTO @Deal VALUES (1)
INSERT INTO @Deal VALUES (2)

SELECT d.ArchiveDealStatus, a.Name
FROM   @Deal d 
LEFT JOIN @ArchiveReason a ON a.ArchivedDealID = d.ArchiveDealStatus

Open in new window

I also corrected some typo's in your query (spaces between table alias and column, misspelled field).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial