asked on

Help with join

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

dqmq

How are you sending null values in your parameters?

dqmq

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

gogetsome

ASKER

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

dqmq

>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

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gogetsome

ASKER

Thank you very much!!!

dqmq

Does that not lose the "like" functionality for the status and postdate arguments?

Anthony Perkins

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. :)