Search Details - 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

Search Details

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2008-11-10 : 01:54:41

Hi friends,

I had two tables Kr_request and kr_requestreply

kr_request had fields-reqid,topic,description,abusive,deleted
kr_rerquestreply had fields-reqid,topic,description,abusive ,deleted

select distinct r.* from kr_request as r
join KR_RequestReply rr ON r.ReqId=rr.ReqId
where r.topic like '%test%' or r.Description like '%test%' or rr.Description Like '%test%' or rr.topic like '%test%'
and rr.Abusive = 0 and rr.Deleted= 0 and r.Abusive = 0 and r.Deleted=0

here i am getting the correct results but this condition is not working - rr.Abusive = 0 and r.Abusive = 0.i am getting rr.Abusive = 1 and r.Abusive = 1 results also.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 01:58:58

try like below

select distinct r.* from kr_request as r 
join KR_RequestReply rr ON r.ReqId=rr.ReqId 
where (r.topic like '%test%' or r.Description like '%test%' or rr.Description Like '%test%' or rr.topic like '%test%') 
and rr.Abusive = 0 and rr.Deleted= 0 and r.Abusive = 0 and r.Deleted=0

Jason100
Starting Member

34 Posts

Posted - 2008-11-10 : 01:59:39

SELECT r.
FROM
(
SELECT *
FROM KR_RequestReply
WHERE Abusive = 0
AND Deleted =0
) rr
JOIN
(
SELECT *
FROM kr_request
WHERE Abusive = 0
AND Deleted =0
) r
ON r.ReqId=rr.ReqId
where r.topic like '%test%'
or r.Description like '%test%'
or rr.Description Like '%test%'
or rr.topic like '%test%'

Kotti
Posting Yak Master

129 Posts

Posted - 2008-11-10 : 04:30:54

Thank you all ,I got the answer

Kotti
Posting Yak Master

129 Posts

Posted - 2008-11-10 : 05:18:52

Sorry Guys ,

I need a small change in the query.

select distinct r.* from kr_request as r
join KR_RequestReply rr ON r.ReqId=rr.ReqId
where (r.topic like '%test%' or r.Description like '%test%' or rr.Description Like '%test%' or rr.topic like '%test%')
and rr.Abusive = 0 and rr.Deleted= 0 and r.Abusive = 0 and r.Deleted=0

here we are checking r.ReqId=rr.ReqId so i am getting the results for only the requests which are
all replied .i need the request details eventhough it was not replied.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 05:25:31

[code]select distinct r.* from kr_request as r
left join KR_RequestReply rr ON r.ReqId=rr.ReqId
where (r.topic like '%test%' or r.Description like '%test%' or rr.Description Like '%test%' or rr.topic like '%test%')
and rr.Abusive = 0 and rr.Deleted= 0 and r.Abusive = 0 and r.Deleted=0
[/code]

Kotti
Posting Yak Master

129 Posts

Posted - 2008-11-10 : 05:38:03

i am not getting the result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 05:40:10

try this too

select distinct r.* from kr_request as r 
left join KR_RequestReply rr ON r.ReqId=rr.ReqId 
on (r.topic like '%test%' or r.Description like '%test%' or rr.Description Like '%test%' or rr.topic like '%test%') 
and rr.Abusive = 0 and rr.Deleted= 0 and r.Abusive = 0 and r.Deleted=0

if not working, then post some sample data

Kotti
Posting Yak Master

129 Posts

Posted - 2008-11-10 : 05:56:30

Getting error as Incorrect syntex near on.

Sample Data:
kr_request

reqid Topic Description abusive deleted
1 Test 1 Test is going1 0 0
2 Test 2 Test is going2 0 0
3 Test 3 Test is going3 0 0
4 complete Work 0 0
5 Test 4 Test is going4 0 0

RepId reqid Topic Description abusive deleted
1 2 Sample1 Sample Test1 0 0
2 3 Sample2 Sample Test 2 0 0
3 1 Sample3 Sample Test 3 0 0
4 1 Sample4 Sample Test 4 0 0
5 5 Test Completed 0 0

When i search using this query
select distinct r.* from kr_request as r
left join KR_RequestReply rr ON r.ReqId=rr.ReqId
on (r.topic like '%Complete%' or r.Description like '%Complete%' or rr.Description Like '%Complete%' or rr.topic like '%Complete%')
and rr.Abusive = 0 and rr.Deleted= 0 and r.Abusive = 0 and r.Deleted=0

I am getting the result for only reqid=5 but not for reqid=4 since for reqid=4 no one was replied.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 05:59:42

Try this

select distinct r.* 
from kr_request as r 
left join KR_RequestReply rr 
ON r.ReqId=rr.ReqId 
AND (r.topic like '%Complete%' or r.Description like '%Complete%' or rr.Description Like '%Complete%' or rr.topic like '%Complete%') 
and rr.Abusive = 0 
and rr.Deleted= 0 
and r.Abusive = 0 
and r.Deleted=0

Kotti
Posting Yak Master

129 Posts

Posted - 2008-11-10 : 06:33:14

No i am not getting the results

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 06:55:06

[code]declare @kr_request table
(
reqid int,
Topic varchar(30),
Description varchar(20),
abusive bit,
deleted bit
)

insert into @kr_request
select 1, 'Test 1', 'Test is going1', 0, 0 union all
select 2, 'Test 2', 'Test is going2', 0 ,0 union all
select 3, 'Test 3' ,'Test is going3', 0 ,0 union all
select 4 ,'complete', 'Work', 0, 0 union all
select 5 ,'Test 4' ,'Test is going4', 0, 0

declare @KR_RequestReply table
(
RepId int,
reqid int,
Topic varchar(50),
Description varchar(50),
abusive bit,
deleted bit
)
insert into @KR_RequestReply
select 1, 2, 'Sample1', 'Sample Test1', 0, 0 union all
select 2, 3, 'Sample2', 'Sample Test 2', 0, 0 union all
select 3, 1, 'Sample3', 'Sample Test 3', 0, 0 union all
select 4, 1, 'Sample4', 'Sample Test 4', 0, 0 union all
select 5, 5, 'Test', 'Completed', 0 ,0

select distinct r.*
from @kr_request as r
left join @KR_RequestReply rr
ON r.ReqId=rr.ReqId
WHERE (r.topic like '%Complete%' or r.Description like '%Complete%' or rr.Description Like '%Complete%' or rr.topic like '%Complete%')
and isnull(rr.Abusive,0) = 0
and isnull(rr.Deleted,0)= 0
and r.Abusive = 0
and r.Deleted=0

output
----------------------------------------
reqid Topic Description abusive deleted
4 complete Work 0 0
5 Test 4 Test is going4 0 0 [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources