Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with join

Posted on 2011-05-07
8
Medium Priority
?
203 Views
Last Modified: 2012-05-11
Hello, with the attached code, if I send in only null values for all parameters then I should return all rows in Blog_Posts. However, I only get rows that also contain values in Blog_PostCategories. How can I modify this select statement to include all the rows. I think my join is incorrect and could use some help please.

Select convert(varchar(50), bp.Postid) as PostId, bp.Title, bp.PostDate, bp.[Status], bp.Visibility, bp.CreatedBy, bp.AllowComments
from Blog_Posts bp,  Blog_PostCategories bpc
where
(rtrim(@Title)  is NUll or rtrim(bp.Title)  like rtrim(@Title) + '%')
and (rtrim(@CreatedBy)  is NUll or rtrim(bp.CreatedBy)  like rtrim(@CreatedBy) + '%')
and (rtrim(@Status)  is NUll or rtrim(bp.[Status])  like rtrim(@Status) + '%')
and (rtrim(@PostDate)  is NUll or rtrim(DATEADD(dd, 0, DATEDIFF(dd, 0, bp.PostDate)))  like rtrim(@PostDate) + '%')--and (rtrim(@PostDate)  is NUll or rtrim(DATEADD(dd, 0, DATEDIFF(dd, 0, bp.PostDate)))  like rtrim(@PostDate) + '%')
and (rtrim(@CategoryId)  is null or rtrim(bpc.CategoryId)  like rtrim(@CategoryId) + '%')
and  bpc.PostId=bp.PostId
create PROCEDURE [dbo].[Blog_SearchPosts] 
@Title varchar (100),
@CreatedBy varchar (50),
@CategoryId uniqueidentifier,
@Status int,
@PostDate datetime
AS
 
--drop table #Posts
Create Table #Posts(

PostId varchar(50),
Title varchar(100),
PostDate datetime,
[Status] int,
Visibility int, 
CreatedBy varchar(50),
AllowComments bit

)

--Declare @Title varchar(100) 
--Set @Title = NUll
--Declare @CreatedBy varchar(50) 
--set @Createdby = NUll
--Declare @CategoryId uniqueidentifier  
--set @CategoryId = NUll
--Declare @Status int 
--set @Status =  NUll
--Declare @PostDate datetime 
--set @PostDate = NUll

Insert #Posts
Select convert(varchar(50), bp.Postid) as PostId, bp.Title, bp.PostDate, bp.[Status], bp.Visibility, bp.CreatedBy, bp.AllowComments
from Blog_Posts bp,  Blog_PostCategories bpc
where 
(rtrim(@Title)  is NUll or rtrim(bp.Title)  like rtrim(@Title) + '%')
and (rtrim(@CreatedBy)  is NUll or rtrim(bp.CreatedBy)  like rtrim(@CreatedBy) + '%')
and (rtrim(@Status)  is NUll or rtrim(bp.[Status])  like rtrim(@Status) + '%')
and (rtrim(@PostDate)  is NUll or rtrim(DATEADD(dd, 0, DATEDIFF(dd, 0, bp.PostDate)))  like rtrim(@PostDate) + '%')--and (rtrim(@PostDate)  is NUll or rtrim(DATEADD(dd, 0, DATEDIFF(dd, 0, bp.PostDate)))  like rtrim(@PostDate) + '%')
and (rtrim(@CategoryId)  is null or rtrim(bpc.CategoryId)  like rtrim(@CategoryId) + '%')
and  bpc.PostId=bp.PostId


Select distinct(convert(varchar(50), Postid)) as PostId, Title, PostDate, [Status], Visibility, CreatedBy, AllowComments
--from blog_posts
From #Posts order by PostDate Desc

Open in new window

0
Comment
Question by:gogetsome
  • 4
  • 2
  • 2
8 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 35713844
How are you sending null values in your parameters?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 35713848
P.S. you don't need the temp table and there is no point in trimming a parameter whilst comparing it to null

@CreatedBy Is NULL

is equivalent to

trim(@CreatedBy) is NULL
0
 

Author Comment

by:gogetsome
ID: 35714150
I'm sending from .net using System.DBNull.Value. Your right there is no need for a temp table.  So, is my join correct? I don't really need this and either?
and  bpc.PostId=bp.PostId
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Expert Comment

by:dqmq
ID: 35717257
>So, is my join correct?
It's syntactically correct; though an older ASNI standard.


This is equivalent and preferred:
...
from Blog_Posts bp,  Blog_PostCategories bpc on bpc.PostId=bp.PostId
where...

That said, I don't know your data so I cannot say if the join is logically correct.  Your join implies another table for "Categories", and a many-many relationship between Blog_Posts and categories.  If that's correct, then I would have to say your join is correct.
:<)

>I don't really need this and either (and bpc.PostId=bp.PostId)?
With the old style join you need it; with the new style join you don't
 
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 35717390
Try it this way:
CREATE PROCEDURE [dbo].[Blog_SearchPosts]
    @Title varchar(100),
    @CreatedBy varchar(50),
    @CategoryId uniqueidentifier,
    @Status int,
    @PostDate datetime

AS --drop table #Posts

--Declare @Title varchar(100) 
--Set @Title = NUll
--Declare @CreatedBy varchar(50) 
--set @Createdby = NUll
--Declare @CategoryId uniqueidentifier  
--set @CategoryId = NUll
--Declare @Status int 
--set @Status =  NUll
--Declare @PostDate datetime 
--set @PostDate = NUll

SELECT	@Title = RTRIM(@Title) + '%',
	@CreatedBy = RTRIM(@CreatedBy) + '%'

SELECT  DISTINCT CONVERT(varchar(50), bp.Postid) AS PostId,
        bp.Title,
        bp.PostDate,
        bp.[Status],
        bp.Visibility,
        bp.CreatedBy,
        bp.AllowComments
FROM    Blog_Posts bp
        INNER JOIN Blog_PostCategories bpc ON bp.PostId = bpc.PostId
WHERE   (@Title IS NULL OR bp.Title LIKE @Title)
        AND (@CreatedBy IS NULL OR bp.CreatedBy LIKE @CreatedBy)
        AND (@Status IS NULL OR bp.[Status] = @Status)
        AND (@PostDate IS NULL OR DATEADD(day, 0, DATEDIFF(day, 0, bp.PostDate)) = @PostDate)
        AND (@CategoryId IS NULL OR bpc.CategoryId = @CategoryId)
ORDER BY 
	PostDate DESC

Open in new window

0
 

Author Closing Comment

by:gogetsome
ID: 35717429
Thank you very much!!!
0
 
LVL 42

Expert Comment

by:dqmq
ID: 35717473
Does that not lose the "like" functionality for the status and postdate arguments?  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35717480
I did not see any point in doing a LIKE on an integer or a datetime.  But if the author thinks it is important, they can certainly add it back on.   :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question