SQL Server: trouble with selection from derived table

Hi,

MyTable:
ID: 1
ID: 3
ID: 4

MyNames:
ID: 1   Name: Name1
ID: 2   Name: Name2
ID: 3   Name: Name3

SELECT  MyTable.ID, MyNames.Name
FROM  MyTable RIGHT OUTER JOIN
                      MyNames ON MyTable.ID = MyNames.ID

Open in new window

Result:
ID: 1   Name: Name1
ID: 3   Name: Name3
ID: 4   Name: NULL

How can I accomplish that I get the records where Name = NULL?

I've tried to add:
WHERE     (MyNames.Name IN ('', NULL))

Open in new window

But this results in zero records.
Stef MerlijnDeveloperAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..

WHERE ISNULL(MyNames.Name, '') = ''
0
 
HugoHiaslCommented:
Either try

WHERE MyNames.Name IN ('') OR MyNames.Name IS NULL

If this does not work try using LEFT OUTER JOIN

SELECT  MyTable.ID, MyNames.Name
FROM  MyNames LEFT OUTER JOIN
                      MyTable ON MyTable.ID = MyNames.ID
WHERE MyNames.Name IN ('') OR MyNames.Name IS NULL
                                  

Open in new window

0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Absolutely not the biggest SQL routined there is but

ID: 4   Name: NULL

It is the result because in Mynames you havent got id4, am i missing a point?

*edit* ah, from the query above i understand you only want to show where name is NULL reported.
0
 
Stef MerlijnDeveloperAuthor Commented:
The first posted solution works as a charme. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.