NULL Issue - 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

Transact-SQL (2000)

NULL Issue

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-09-02 : 02:25:07

select a.emp_id,b.emp_stat from
tbl_emp_details as a,
tbl_emp_rules as b,
tbl_check_list as c
where
a.ref_id=b.ref_id and
b.emp_id=c.emp_id and
a.check_number=c.checknumber and
a.account_number=c.accountnumber

Here basically tbl_emp_details and tbl_check_list have the check_number and account number in common
Here Actual combination gives 350 records
But when u do this query it gives 300 only 50 of them have check_number as null so its not getting that
records here.So is there anything i can add to my query to get the values

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 02:47:03

Do you mean like this?

Change a.check_number=c.checknumber to:


(
    a.check_number=c.checknumber 
    OR 
    (
           a.check_number IS NULL
       AND c.checknumber IS NULL
    )
)

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-09-02 : 02:55:43

will ISNULL(a.check_number,'A')=ISNULL(c.checknumber) work

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-02 : 03:04:04

Have you tried Kris method?

Madhivanan

Failing to plan is Planning to fail

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-09-02 : 03:12:15

I tried...when I do both I get the same result.....Just want to clarify also whether the method of ISNULL will work or not...
No multiple records are coming

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 03:44:50

Yup, IsNull would work (you need to add a second parameter 'A' in your second instance). You MUST also use a value which CANNOT exist in the data columns, now or in the future! For that reason I prefer the explict IS NULL checks.

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-09-02 : 04:19:29

This is the final query i have done but its taking long to run....Check_number and account_number won't be having 'A and B' in future also

select a.emp_id,b.emp_stat from
tbl_emp_details as a,
tbl_emp_rules as b,
tbl_check_list as c
where
a.ref_id=b.ref_id and
b.emp_id=c.emp_id and
ISNULL(a.check_number,'A')=ISNULL(c.checknumber,'A') and
ISNULL(a.account_number,'B')=ISNULL(c.accountnumber ,'B') and
c.dateentered =(
Select max(dateentered) from tbl_check_list
where ISNULL(checknumber,'A')=ISNULL(a.check_number,'A') and
ISNULL(accountnumber,'B')=ISNULL(a.account_number ,'B') and
emp_id=b.emp_id
group by emp_id
)

here the problem is in the checklist table there may be mutiple records for the same check number,account number and emp_id so i had to get the max of the date entered to get the latest one among those.Is there any way we can rewrite and get an optimized result

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 05:33:57

Might be worth calculating the Max Dates first:


SELECT	c.emp_id AS T_emp_id,
	MAX(c.dateentered) AS T_MaxDate
INTO	#MyTempTable
FROM	dbo.tbl_emp_details AS a
	JOIN dbo.tbl_emp_rules AS b
		ON b.ref_id = a.ref_id
	JOIN dbo.tbl_check_list AS c
		ON c.emp_id = b.emp_id
WHERE	    ISNULL(a.check_number, 'A') = ISNULL(c.checknumber, 'A') 
	AND ISNULL(a.account_number, 'B') = ISNULL(c.accountnumber , 'B')
GROUP BY c.emp_id

SELECT	a.emp_id,
	b.emp_stat 
FROM	dbo.tbl_emp_details AS a
	JOIN dbo.tbl_emp_rules AS b
		ON b.ref_id = a.ref_id
	JOIN dbo.tbl_check_list AS c
		ON c.emp_id = b.emp_id
	JOIN #MyTempTable T
		 ON T.T_emp_id = b.emp_id
		AND T.T_MaxDate = c.dateentered
WHERE	    ISNULL(a.check_number, 'A') = ISNULL(c.checknumber, 'A') 
	AND ISNULL(a.account_number, 'B') = ISNULL(c.accountnumber , 'B')

DROP TABLE #MyTempTable

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-09-02 : 15:05:57

Thanks a lot I got it and updated the database...Thanks a lot......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources