Meaningful text vs. NULLs in LEFT OUTER JOIN

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)

Meaningful text vs. NULLs in LEFT OUTER JOIN

Author

Topic

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-03-07 : 14:00:06

I'm selecting data from invcdtl, part, and prodgrup tables. Some rows in the InvcDtl table have a NULL PartNum. Some rows in the Part table have a NULL ProdCode. In order to get all invoices in my result set, I'm using LEFT OUTER JOINs.

--Here are my tables:
CREATE TABLE invcdtl (
invoicenum int NOT NULL ,
partnum varchar (50) NULL
)

CREATE TABLE part (
partnum varchar (50) NOT NULL ,
prodcode varchar (8) NULL
)

CREATE TABLE prodgrup (
prodcode varchar (8) NOT NULL ,
description varchar (30) NULL
)

--Here is my query:
SELECT i.invoicenum
, p.partnum
,g.prodcode
FROM invcdtl i
LEFT OUTER JOIN part p on i.partnum=p.partnum
LEFT OUTER JOIN prodgrup g on p.prodcode=g.prodcode

Here is my output:
...
12054 NULL NULL
12055 NULL NULL
10822 003121915P001 GE
11631 003121915P001 GE
...

I know some of the NULLs indicate that the invoice record did not have a part identified while other NULLs indicate that the part record did not have a prodgrup identified. How can I replace the NULLs in my result set with meaningful text that indicates exactly where each of the NULLs is coming from?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-07 : 14:04:10

Huh?
The nulls in the part column indicate there was not part number identified.

The nulls in the prod column indicate there was no product group identified.

e4 d5 xd5 Nf6

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-07 : 14:05:23

Not 100% sure you want as you did not provide expected output, but maybe somethng lile this??

SELECT 
	i.invoicenum
, COALESCE(p.partnum, 0) AS PartNum -- No PartNum
,CASE
	WHEN p.partnum IS NULL THEN COALESCE(g.prodcode, -1) -- No PartNum, then no product code
	ELSE COALESCE(g.prodcode, 0) -- Part Num exists, but no product code
END AS PartNum
FROM invcdtl i
LEFT OUTER JOIN part p on i.partnum=p.partnum
LEFT OUTER JOIN prodgrup g on p.prodcode=g.prodcode

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-03-07 : 14:16:43

I get it. Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources