Help with joined select when bitfield is used

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)

Help with joined select when bitfield is used

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-05-08 : 05:36:16

Hi

I know this is probably very hard to figure out without the full table data, but any pointers would be helpful.

In the following statement (with two commented out lines) I see the results I expect.
Within these results, all the values for the sg.archived (bit) column display as 0.

When I remove the comments though, I get zero rows in my results. I can't figure out why this happens considering the values are all 0 anyway.

Can anyone see what I'm doing wrong?


SELECT
	*
FROM
	[tbl_u] u INNER JOIN
	[tbl_sol] sol ON u.solID = sol.ID INNER JOIN
	[tbl_ol] ol ON sol.olID = ol.olID INNER JOIN
	[tbl_si] si ON si.ID = sol.siID LEFT OUTER JOIN
	[tbl_sgu] sgu ON sgu.uID = u.uID LEFT OUTER JOIN
	[tbl_sg] sg ON sgu.sgID = sgsgID LEFT OUTER JOIN
	[tbl_sgs] sgs ON sgs.sgID = sg.sgID LEFT OUTER JOIN
	[tbl_S] s ON s.sID = sgs.sID
WHERE
	(u.customerID = 15) AND
	(u.archived = 0) AND
	(sg.sgID = @sgID OR @sgID IS NULL) AND
	(s.sID = @sID OR @sID IS NULL) AND
	-- removed -- (sg.archived = 0)  AND
	(si.ID = @siID OR @siID IS NULL) AND
	(ol.olID = @dID OR @dID IS NULL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-08 : 06:23:12

[code]SELECT *
FROM [tbl_u] u
INNER JOIN [tbl_sol] sol ON u.solID = sol.ID
INNER JOIN [tbl_ol] ol ON sol.olID = ol.olID
INNER JOIN [tbl_si] si ON si.ID = sol.siID
LEFT JOIN [tbl_sgu] sgu ON sgu.uID = u.uID
LEFT JOIN [tbl_sg] sg ON sgu.sgID = sg.sgID
AND (sg.sgID = @sgID OR @sgID IS NULL)
AND (sg.archived = 0)
LEFT JOIN [tbl_sgs] sgs ON sgs.sgID = sg.sgID
LEFT JOIN [tbl_S] s ON s.sID = sgs.sID
AND (s.sID = @sID OR @sID IS NULL)
WHERE u.customerID = 15
AND u.archived = 0
AND (si.ID = @siID OR @siID IS NULL)
AND (ol.olID = @dID OR @dID IS NULL)[/code]

E 12°55'05.63"
N 56°04'39.26"

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-05-08 : 06:38:55

Peso

You are a T-SQL superstar - that works like a charm!

Please tell me, what is the difference in applying the filter in the JOIN instead of in the WHERE clause? Why does SQL produce different results for each?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-08 : 06:40:59

Because of the outer joins (LEFT JOIN).

Thank you for your feedback.

E 12°55'05.63"
N 56°04'39.26"

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-05-08 : 08:38:33

Ah yes, I've just done some testing based on your feedback. Here's my findings for anyone suffering the same problem.

It happens because the LEFT JOIN creates NULLs in the results, so by adding the WHERE clause filter, we end up removing those NULLs, when we actually needed to see them. If we JOIN the table and remove the 0 values at that stage, the results are unaffected.

Now that that's clear, I can once again have my sanity back...

Thanks Peso! :-D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 05:14:16

quote:
Originally posted by R

Ah yes, I've just done some testing based on your feedback. Here's my findings for anyone suffering the same problem.

It happens because the LEFT JOIN creates NULLs in the results, so by adding the WHERE clause filter, we end up removing those NULLs, when we actually needed to see them. If we JOIN the table and remove the 0 values at that stage, the results are unaffected.

Now that that's clear, I can once again have my sanity back...

Thanks Peso! :-D

this article explains it

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-05-09 : 05:30:10

"Never reference OUTER JOIN-ed tables in your WHERE clause."

That's a really informative article. Thanks visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 05:34:22

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources