Complex join with where clause troubles

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)

Complex join with where clause troubles

Author

Topic

KHeon
Posting Yak Master

135 Posts

Posted - 2003-03-04 : 09:19:07

Hello! I've built a stored procedure that is fairly intelligent that I use to filter press releases based on specified criteria at a web-front end. I use COALESCE to handle things and it works great. Problem is I now have a situation where I have a press release that isn't bound to a joined table (and shouldn't) be. The query works fine until I assign the WHERE clause for the column that doesn't exist in the joined table. Below is my sproc code:

CREATE PROCEDURE dbo.usp_GetPressReleaseList
(
@sector_id INT = NULL,
@type_id INT = NULL,
@year CHAR(4) = NULL,
@hidden_flag BIT = 0
)
AS

SET NOCOUNT ON

--- Cleanup values passed in, need NULL to effectively use COALESCE.
IF @sector_id = 0 SET @sector_id = NULL
IF LEN(LTRIM(RTRIM(@year))) = 0 SET @year = NULL

SELECT DISTINCT(p.press_release_id), p.release_date, p.title, p.summary, p.hidden_flag
FROM PRESS_RELEASES p
LEFT JOIN PRESS_RELEASE_SECTOR_MAPS prsm
ON p.press_release_id = prsm.press_release_id
INNER JOIN PRESS_RELEASE_TYPE_MAPS prtm
ON p.press_release_id = prtm.press_release_id
WHERE DATEPART("YYYY", p.release_date) = COALESCE(@year, DATEPART("YYYY", p.release_date))
AND prsm.sector_id = COALESCE(@sector_id, sector_id)
AND prtm.type_id = COALESCE(@type_id, prtm.type_id)
AND CONVERT(DATETIME, p.go_live_date, 101) <= CONVERT(DATETIME, GETDATE(), 101)
AND p.hidden_flag = COALESCE(@hidden_flag, hidden_flag)

SET NOCOUNT OFF

The bold is the offending clause. Can anyone offer me some insight as to how I can keep this sproc working without having to sacrifice my use of the WHERE/COALESCE sections?

Also, I know I could use a temp table or table datatype to store the immediate results and then return a filtered list from this but I'm hoping to avoid that if I can, it just doesn't seem necessary.

NOTE: FYI - These tools are fairly unuasable (and viewable) in Mozilla.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-04 : 10:09:04

What do you mean by:

Problem is I now have a situation where I have a press release that isn't bound to a joined table (and shouldn't) be!

What does that mean in SQL terms?

Brett

8-)

KHeon
Posting Yak Master

135 Posts

Posted - 2003-03-04 : 10:20:19

I have a sectors and a types table. Some of the press releases are bound are related in a sectors table (same release could belong to multiple sectors) and some belong to multiple types. For the majority of the releases a press release will be related to both a sector (at least one) and a type (at least one), but there are some situations where this won't be the case. My procedure blows fails to return these unrelated releases.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources