Silly NULL question - 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

General SQL Server Forums

New to SQL Server Programming

Silly NULL question

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-04-24 : 16:11:17

Okay, so here's my deal lets say i have two tables

Table 1:
Firstname
Lastname
Address
City
State
Zip
ID

Table 2:
ID
DOB
Income

now if i do a query like:

select firstname,lastname,dob from table1 join table2 on table1.id = table2.id

i get all the records that MATCH right? now lets say i dont have a matching record in table 2 for every record in table 1, i would do:

select firstname,lastname,dob from table1 left join table2 on table1.id = table2.id

but i would get NULL for every record i DONT match right? how can i make it so those NULLS display as blank instead? (without doing a case for every column)

make sense? i'm hoping this is an easy option to change.

Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-24 : 16:34:25

you can use coalesce rather than case but it's similar.
Other than that do it in the client - you say display so whatever is doing the display at something to convert null to blank.
You wouldn't be able to do thins in sql without converting numerics to character - which will happen anyway as part of the display function.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-04-24 : 16:40:12

Oh Boo! :) thanks!

ljg
Starting Member

4 Posts

Posted - 2011-04-25 : 08:47:00

To convert null to blank u can use isnull function
eg : select isnull(fieldname,'') as name from tablename

lj

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-04-25 : 09:53:01

i'm looking for more of a blanket option, kinda like

Set I_DONT_WANT_NULLS On :)

this table has something in the area of about 300 fields over 5 tables. that's a lot of isnull(a,''),isnull(b,'') etc... etc...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-25 : 10:12:09

This is really a display issue, which should be done in the front end. But if the front-end guys won't do it, there's no way around a bunch of isnull statements.

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-25 : 10:16:24

The proper way to fix this is...wait for it...make the columns NOT NULL.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-04-25 : 10:46:59

the columns arent null, it has to do with doing left joins (which will return a NULL value for everything that does not match)

a hard example would be for my business data, we have company,address,city,state,zip,uid for all the records

on the title file we have title,fname,lname,uid for every record that we have contact info for.

so now lets say i want every company in AZ, but i want to display all records w/ contact info where available.


select * from company c left join title t on t.uid = c.uid

this would then give me something like:

mycompany,123 any st,phoenix,az,85012,1234,NULL,NULL,NULL,NULL

for any record w/o a contact match. i'm looking to get something like:
mycompany,123 any st,phoenix,az,85012,1234,,,,

with out all the work :)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-25 : 10:53:17

Oooops, missed that part, sorry.

As Jim said, it's really a display issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources