Join Problem - 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

SQL Server Development (2000)

Join Problem

Author

Topic

josethegeek
Starting Member

45 Posts

Posted - 2003-08-18 : 18:12:40

I would really appreciate if someone could get me out with a Join problem.

I currently have an Articles table(tbl_articles), and Authors table(tbl_authors), and a table that contains the relations between the articles and authors table (tbl_ArticleAuthors). An article can have many authors or have none. Below is the table structure

tbl_Articles
-Article_ID
-Article_Title

tbl_Authors
-Author_ID
-Author_Name

tbl_ArticleAuthors
-Article_ID
-Author_ID

A user can limit the articles by an author. If they select an author, they will only be shown articles by that author, if not they will be shown all the articles. The problem is that if an article doesn't have an author, that article will not show up because there will be a join between the tbl_articles table and the tbl_ArticleAuthors table. I want to show all the articles if the user does not select and author. Could some one please help me??? I know this could be done with 2 stored procedures and ASP. Just have an IF statement, and according to the condition execute one of the 2 stored procedures, I want to be able to do it with only one stored procedure. Thank you.

This is what I have so far

CREATE PROCEDURE sp_PagingArticles
(
@PageSize integer,
@PageNumber integer,
@TotalRecords integer OUTPUT,
@Active integer = NULL,
@ArticleID integer = NULL,
@ArticleTitle varchar(255) = NULL,
@AuthorID integer = NULL
)

AS

SELECT Article_ID FROM tbl_Articles WHERE (Article_ID = COALESCE(@ArticleID, Article_ID)) AND (Article_Title LIKE COALESCE(@ArticleTitle, Article_Title))

PS I might have confused a lot of people, if so let me know. Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-18 : 18:18:58

[code]
CREATE PROCEDURE sp_PagingArticles
(
@PageSize integer,
@PageNumber integer,
@TotalRecords integer OUTPUT,
@Active integer = NULL,
@ArticleID integer = NULL,
@ArticleTitle varchar(255) = NULL,
@AuthorID integer = NULL
)
AS

IF @AuthorID IS NULL
SELECT Article_ID
FROM tbl_Articles
ELSE
SELECT a.Article_ID
FROM tbl_Articles a
INNER JOIN tbl_ArticleAuthors aa ON a.Article_ID = aa.Article_ID
WHERE aa.Author_ID = @AuthorID

RETURN
[/code]

Also, do not ever name stored procedures with sp_. SQL Server will check to see if the object exists in the master database before it checks the user database.

Tara

josethegeek
Starting Member

45 Posts

Posted - 2003-08-18 : 18:26:20

Thank you. But it will get a little messy if I had IF statements inside the Stored Procedure.

To make it easier, I only had the Authors relation on my initial question. But in reality there will be many relations, because an article can have many/none countries, authors, type(news, press release).

So it would be more like this

CREATE PROCEDURE sp_PagingArticles
(
@PageSize integer,
@PageNumber integer,
@TotalRecords integer OUTPUT,
@Active integer = NULL,
@ArticleID integer = NULL,
@ArticleTitle varchar(255) = NULL,
@AuthorID integer = NULL,
@ArticleTypeID integer = NULL,
@CountryID integer = NULL
)

AS

SELECT Article_ID FROM tbl_Articles WHERE (Article_ID = COALESCE(@ArticleID, Article_ID)) AND (Article_Title LIKE COALESCE(@ArticleTitle, Article_Title))

Hope it didn't confuse anyone. Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-18 : 18:29:22

Why would it get messy if you have IF statements in the stored procedure?

Tara

josethegeek
Starting Member

45 Posts

Posted - 2003-08-19 : 01:13:57

quote:
Originally posted by tduggan

Why would it get messy if you have IF statements in the stored procedure?

Tara

Well the user can limit the search to multiple criterias. So they can search for an article of Type "News" and an article that falls under China. So if I used the IF method it would get sloppy. Thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-19 : 14:34:45

I still don't understand why it would get sloppy. As long as you following coding standards, the stored procedure would be readable and understandable.

Tara

josethegeek
Starting Member

45 Posts

Posted - 2003-08-19 : 19:12:10

quote:
Originally posted by tduggan

I still don't understand why it would get sloppy. As long as you following coding standards, the stored procedure would be readable and understandable.

Tara

Thank you for your help. The reason I didnt want to use IF statements was because the procedure took in many parameters, and then I would have to have a dynamic sql query. After looking around for answers, I decided to try some things out, and finally found how to do it.

CREATE PROCEDURE sp_PagingArticles
(
@PageSize integer,
@PageNumber integer,
@TotalRecords integer OUTPUT,
@Active integer = NULL,
@ArticleID integer = NULL,
@ArticleTitle varchar(255) = NULL,
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@ArticleTypeID integer = NULL,
@CenterID integer = NULL,
@CountryID integer = NULL,
@GeoRegionID integer = NULL,
@GloblaRegionID integer = NULL
)

AS

CREATE TABLE #tbl_SearchResultsArticles(
result_number integer identity(1,1),
result_id integer
)

INSERT INTO #tbl_SearchResultsArticles(result_id)
SELECT Articles.Article_ID FROM tbl_Articles As Articles
LEFT OUTER JOIN tbl_ArticleArticleTypes AS ArticleArticleTypes ON Articles.Article_ID = ArticleArticleTypes.Article_ID
LEFT OUTER JOIN tbl_ArticleCenters AS ArticleCenters ON Articles.Article_ID = ArticleCenters.Article_ID
LEFT OUTER JOIN tbl_ArticleCountrys AS ArticleCountrys ON Articles.Article_ID = ArticleCountrys.Article_ID
LEFT OUTER JOIN tbl_ArticleGeoRegions AS ArticleGeoRegions ON Articles.Article_ID = ArticleGeoRegions.Article_ID
LEFT OUTER JOIN tbl_ArticleGlobalRegions AS ArticleGlobalRegions ON Articles.Article_ID = ArticleGlobalRegions.Article_ID
WHERE (Articles.Article_Active = COALESCE(@Active, Articles.Article_Active)) AND (Articles.Article_ID = COALESCE(@ArticleID, Articles.Article_ID)) AND (Articles.Article_Title LIKE COALESCE(@ArticleTitle, Articles.Article_Title)) AND (Articles.Article_Date >= COALESCE(CAST(@StartDate AS CHAR), Articles.Article_Date) AND Articles.Article_Date <= COALESCE(CAST(@EndDate AS CHAR), Articles.Article_Date))
AND ArticleArticleTypes.ArticleType_ID = COALESCE(@ArticleTypeID, ArticleArticleTypes.ArticleType_ID, NULL)
ORDER BY Articles.Article_Date DESC

SET @TotalRecords = @@ROWCOUNT

DECLARE @StartRecord int
DECLARE @EndRecord int
SET @StartRecord = (((@PageNumber - 1) * @PageSize) + 1)
SET @EndRecord = (@StartRecord + @PageSize - 1)

SELECT Articles.Article_ID, Articles.Article_Title, Articles.Article_Active, Articles.Article_Date, Articles.Article_Abstract, Articles.Article_Thumbnail FROM #tbl_SearchResultsArticles AS SR, tbl_Articles AS Articles WHERE (SR.result_id = Articles.Article_ID) AND (SR.result_number >= @StartRecord) AND (SR.result_number <=@EndRecord) ORDER BY Articles.Article_Date DESC

DROP TABLE #tbl_SearchResultsArticles
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-19 : 19:18:19

You might want to calculate the end record before the query and do a set rowcount to that value so that you aren't populating the temp table with unnecessary records.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

josethegeek
Starting Member

45 Posts

Posted - 2003-08-21 : 01:15:22

quote:
Originally posted by nr

You might want to calculate the end record before the query and do a set rowcount to that value so that you aren't populating the temp table with unnecessary records.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Yeah, thanks alot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources