Help with SELECT statement - 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 2005 Forums

Transact-SQL (2005)

Help with SELECT statement

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-10-01 : 05:24:48

Hi All

Please see the following SELECT statement from a stored procedure that uses 2 table variables joined to actual database tables ...


SELECT
	u.userID,
	u2.[name],
	u2.surname,
	l.linkID,			
	ca.[level]
FROM
	@tbl_userIDs u CROSS JOIN
	@tbl_linkIDs l INNER JOIN
	[tbl_TableSGS] sgs ON l.linkID = sgs.linkID LEFT OUTER JOIN
	[tbl_TableCA] ca on ca.userid = u.userid AND ca.skillid = sgs.skillid AND ca.skillgroupid = sgs.skillgroupid INNER JOIN
	[tbl_Users] u2 ON u2.userID = u.userID

ca.[level] in the SELECT statement returns a Numeric(5,2) value, or NULL.

I need to expand this procedure so the user can specify upper and lower boundary values to search for,
e.g. only show results where ca.[level] is between 75 and 100. I will do this with 2 parameters, @upper and @lower.

Here's the catch though. If I put this in the WHERE clause, the rows are omitted from the results when the value of ca.[level] is outside the search boundaries. I need the results to still include the rows where ca.[level] is outside these boundaries, but show ca.[level] as NULL.

I thought a CASE statement in the SELECT might do the trick, but I'm stumped how to achieve it. I think I must first check the parameters are not null, then if not, compare against the actual value of ca.[level] returned, showing either NULL or ca.[level] as required.

Can anyone help me out? I'm even confusing myself trying to explain it...!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 05:29:04

[code]SELECT u.userID,
u2.[name],
u2.surname,
l.linkID,
ca.[level]
FROM @tbl_userIDs AS u
CROSS JOIN @tbl_linkIDs AS l
INNER JOIN [tbl_TableSGS] AS sgs ON sgs.linkID = l.linkID
LEFT JOIN [tbl_TableCA] AS ca on ca.userid = u.userid
AND ca.skillid = sgs.skillid
AND ca.skillgroupid = sgs.skillgroupid
AND ca.level between @param1 and @param2
INNER JOIN [tbl_Users] AS u2 ON u2.userID = u.userID[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 05:29:20

[code]SELECT
u.userID,
u2.[name],
u2.surname,
l.linkID,
ca.[level]
FROM
@tbl_userIDs u CROSS JOIN
@tbl_linkIDs l INNER JOIN
[tbl_TableSGS] sgs ON l.linkID = sgs.linkID LEFT OUTER JOIN
[tbl_TableCA] ca on ca.userid = u.userid AND ca.skillid = sgs.skillid AND ca.skillgroupid = sgs.skillgroupid
AND ca.[level] BETWEEN @lower AND @Upper
INNER JOIN
[tbl_Users] u2 ON u2.userID = u.userID[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 05:30:47

Refer this article for explanation
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 05:31:25

quote:

Originally posted by visakh16

SELECT
	u.userID,
	u2.[name],
	u2.surname,
	l.linkID,			
	ca.[level]
FROM
	@tbl_userIDs u CROSS JOIN
	@tbl_linkIDs l INNER JOIN
	[tbl_TableSGS] sgs ON l.linkID = sgs.linkID LEFT OUTER JOIN
	[tbl_TableCA] ca on ca.userid = u.userid AND ca.skillid = sgs.skillid AND ca.skillgroupid = sgs.skillgroupid 
AND ca.[level] BETWEEN @lower AND @Upper
INNER JOIN
	[tbl_Users] u2 ON u2.userID = u.userID

by 16 secs

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-10-01 : 06:18:22

Thank you very much for your informative posts. Clearly I was really over-complicating this!

The only problem now is that if either of the parameter values are NULL, then the results are all given a NULL value.
If only a @lower parameter value is given, then the results should use all ca.level values that are >= the @lower value.
The same applies to @upper.
If both are NULL, then the search should just fetch all the values as they are.

Can the code be improved to get around this problem?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 06:27:55

[code]AND ca.[level] BETWEEN COALESCE(@lower, ca.[level]) AND COALESCE(@Upper, ca.[level])[/code]

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

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-10-01 : 07:01:12

That works absolutely perfectly.

Thank you sooo much!

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-10-01 : 08:49:55

Hi again. I've posted another question related to this where I need to perform a date check.
If you could help out there I would be most grateful.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111749

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources