SQL returning NULLS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL returning NULLS

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2005-09-09 : 06:47:16

I am using some sql to query 3 tables. When one of the fields is NULL then nothing is returned but I want the code to return values even if the field is NULL.

Code is as follows:

CREATE PROCEDURE sproc_DisplayOrder

(
@ordernum varchar(10)
)

AS

SELECT O.OrderNum, O.PlanRef, O.Title, O.OrderSigned, Os.StatusName, Ob.SignedBy

FROM OrderStatus AS Os JOIN Orders As O
ON Os.StatusID = O.StatusID

JOIN OrderSigned As Ob
ON Ob.SignedId = O.SignedId

WHERE O.OrderNum = @ordernum
GO

The field that will be NULL sometimes will be O.SignedId.
But I want the other values to be returned even if O.SignedId is NULL. And O.SignedId can be returned as NULL value.

Anyone any idea how to do this?

macca

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 06:51:57

This perhaps?


CREATE PROCEDURE sproc_DisplayOrder
(
    @ordernum varchar(10)
)
AS

SELECT O.OrderNum, O.PlanRef, O.Title, O.OrderSigned, Os.StatusName, Ob.SignedBy
FROM OrderStatus AS Os 
     JOIN Orders As O
         ON Os.StatusID = O.StatusID
     LEFT OUTER JOIN OrderSigned As Ob
         ON Ob.SignedId = O.SignedId
WHERE O.OrderNum = @ordernum
GO

Kristen

macca
Posting Yak Master

146 Posts

Posted - 2005-09-09 : 07:29:39

Thanks a million Kristen Man,

That worked a treat.
But how is it that the "LEFT OUTER JOIN" makes the difference?

macca

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 08:05:04

LEFT OUTER JOIN requires that the "parent" data exists, but the "child" data is optional.

You can use a RIGHT OUTER JOIN which works the other way round

Or a FULL OUTER JOIN which gets data from both Parent and Child tables including Children-with-Parents, parents-with-no-children and orphan-children.

Kristen

scrtagt69
Starting Member

3 Posts

Posted - 2005-09-09 : 15:35:35

you can also use something like this...
COALESCE(O.SignedId,'N/A ')
COALESCE checks that one field, if its null, it will return whatever you put between the ' '.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-09 : 15:39:13

quote:
Originally posted by scrtagt69

you can also use something like this...
COALESCE(O.SignedId,'N/A ')
COALESCE checks that one field, if its null, it will return whatever you put between the ' '.

You will still need the LEFT OUTER JOIN though.

Tara

Kristen
Test

22859 Posts

Posted - 2005-09-10 : 01:20:46

"COALESCE(O.SignedId,'N/A ')
COALESCE checks that one field, if its null, it will return whatever you put between the ' '"

Just for to avoid and misunderstandings:

COALESCE returns the first nonnull expression among its arguments of the form
COALESCE ( expression [ ,...n ] )

It does the same job as ISNULL(FirstParam, SecondParam) except that ISNULL only works with two parameters and is NOT standards compliant.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources