case for inner join - 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

General SQL Server Forums

New to SQL Server Programming

case for inner join

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2012-01-31 : 15:14:59

Hi i need a case on inner join as below not sure the best way of doing this??

SELECT *
FROM Revisions r

CASE WHEN @Preview = 0
INNER JOIN RevisionsPreview rl on rl.RevisionId = r.Id
ELSE
INNER JOIN RevisionsLive rl on rl.RevisionId = r.Id

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-31 : 15:19:36

How about creating 2 views. 1 with a join to preview and the other with a join to live. Then test the value of @preview and decide which view to select from?

Duane.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 15:26:23

one way is to use union all

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

craigmacca
Posting Yak Master

142 Posts

Posted - 2012-01-31 : 15:38:53

i cant use views as i need sql to page my results this is the full sp

SELECT r.Id, r.PageId, r.Content, p.PublishStartDate, p.PublishEndDate, r.CreatedDate, r.CreatedBy, r.Teaser,
p.Title, pt.FileLocation as PageTemplate, p.MetaKeywords, p.MetaDescription, u.AliasUrl,
ROW_NUMBER() OVER(ORDER BY t.Ordinal desc) AS rownum
into #tmp_tbl
FROM Revisions r

CASE WHEN @Preview = 0
INNER JOIN RevisionsPreview rl on rl.RevisionId = r.Id
ELSE
INNER JOIN RevisionsLive rl on rl.RevisionId = r.Id

INNER JOIN Pages p on p.Id = r.PageId
INNER JOIN PageTemplates pt on pt.Id = r.TemplateId
INNER JOIN PageToTree ptt ON ptt.PageId = p.Id
INNER JOIN Tree t on t.Id = ptt.TreeId
INNER JOIN UrlAlias u ON u.TreeId = t.Id
WHERE t.ParentId = @TreeId and p.IsActive = 1
AND (PublishStartDate is null or PublishStartDate <= GETDATE())
AND (PublishEndDate is null or PublishEndDate > GETDATE())

CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #tmp_tbl(rownum)

SELECT Id, PageId, Content, PublishStartDate, PublishEndDate, CreatedDate, CreatedBy, Teaser,
Title, PageTemplate, MetaKeywords, MetaDescription, AliasUrl
FROM #tmp_tbl
WHERE rownum BETWEEN (@CurrentPage-1)*@PageSize+1 AND @CurrentPage*@PageSize

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources