how to check for NULL in 2 of 3 values? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

how to check for NULL in 2 of 3 values?

Author

Topic

navs
Starting Member

13 Posts

Posted - 2007-07-22 : 23:05:43

Firstly this code below works ok. It expects a value in w.User_ID, x.User_ID, y.Project_ID, and z.Group_ID only then does it inserts into the table tblMessage. However, the table allows for null values in three of these values, i.e x.User_ID, y.Project_ID, and z.Group_ID. What I need to do is check which of these three is not null, use only that and pass null for the other two. Please help.


INSERT INTO tblMessage SELECT
newid(),
x.User_ID,
@subject,
@content,
'True',
@date,
w.User_ID,
y.Project_ID,
z.Group_ID
from tblUser as w, tblUser as x, tblProject as y , tblGroup as z
where
w.Login_ID = @from
and
x.Login_ID = @to
and
y.Project_Name = @project_name
and
z.Group_Name = @group_name

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-23 : 00:04:39

Use coalesce function which returns first not null value..

Coalesce(x.User_ID, y.Project_ID, z.Group_ID)

--------------------------------------------------
S.Ahamed

navs
Starting Member

13 Posts

Posted - 2007-07-23 : 00:06:16

How do I use it in the script above? Please help

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-23 : 00:11:28

From your query, except w.userid all other ids allows null values and also any of the 4 ids will have value...right?

INSERT INTO tblMessage SELECT
newid(),
x.User_ID,
@subject,
@content,
'True',
@date,
Coalesce(w.User_ID, x.User_ID, y.Project_ID, z.Group_ID),
y.Project_ID,
z.Group_ID
from tblUser as w, tblUser as x, tblProject as y , tblGroup as z
where
w.Login_ID = @from
and
x.Login_ID = @to
and
y.Project_Name = @project_name
and
z.Group_Name = @group_name

--------------------------------------------------
S.Ahamed

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 00:16:33

quote:
Originally posted by navs

Firstly this code below works ok. It expects a value in w.User_ID, x.User_ID, y.Project_ID, and z.Group_ID only then does it inserts into the table tblMessage. However, the table allows for null values in three of these values, i.e x.User_ID, y.Project_ID, and z.Group_ID. What I need to do is check which of these three is not null, use only that and pass null for the other two. Please help.

INSERT INTO tblMessage SELECT
newid(),
x.User_ID,
@subject,
@content,
'True',
@date,
w.User_ID,
y.Project_ID,
z.Group_ID
from tblUser as w, tblUser as x, tblProject as y , tblGroup as z
where
w.Login_ID = @from
and
x.Login_ID = @to
and
y.Project_Name = @project_name
and
z.Group_Name = @group_name

Are you sure the code works ok? Is that the entire code or only part of it? You have 4 tables in your JOIN list and I dont see any join conditions on the tables?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

navs
Starting Member

13 Posts

Posted - 2007-07-23 : 00:41:05

I dont know how to write joins.

This insert is to write a message into a table called tblMessage. Messages can be for a student, a group, or a project. That's why the question of how to use Coalesce. For my original query here at the top, does it really need a join statement? If yes, how?

Thanks
Navs

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 11:44:30

I think you should answer that question as its your data. Check out some of these links:
Read up and understand different types of JOINS. Then look at your data and you will know what to do.

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

navs
Starting Member

13 Posts

Posted - 2007-07-27 : 00:21:53

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources