February 28, 2008 at 2:31 pm
i have two tables t1 and t2 which have one common field f1 among them. for each record in t1 there will be 2 or more record in t2 which can be differentiated using a field f3 in t2. there is also date field f2 in t2. when ever i update t1 a new record is added in t2. i want to retrieve all records from t1 where difference of date field f2 in t2 is less than 5 depending on f3
t1
---
f1 f2
------------
101 abc
102 def
t2
---
f1 f2 f3
------------------------------------
101 02-17-2008 1
102 02-19-2008 1
101 02-25-2008 2
102 02-26-2008 2
when i writwe the query it says the multiple records are being retrieved in where condition
thanks in advance
madan
February 28, 2008 at 3:16 pm
First of all you need to post your query if you want that tweaked. Second a difference of 5 what, days, minutes, seconds, years?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 28, 2008 at 3:39 pm
select a.*,b.f3 from t1 a
inner join t2 b on a.f1=b.f1
where datediff(day,(select b.f2 from t2 where b.f3=2),(select b.f2 from t2 where b.f3 =1)) <5
i want the days comparision
February 28, 2008 at 9:08 pm
This fixes your error, but I don't think it gets the results you are looking for as it returns all the rows.
select
a.*,
b.f3
from
t1 a inner join
t2 b on
a.f1=b.f1
where
datediff(day,(select f2 from t2 where f3=2 and f1 = B.f1),(select f2 from t2 where f3 =1 and f1 = B.f1)) <5
I actually don't think any of the rows in the example data you provide meet your criteria. I think you want to list all the 101 rows where there have been changes within 5 days of each other. In your data there is no data that meets that criteria.
I think this query actually would return the data you want:
Select
a.*,b.first_f3, b.second_f3
FRom
t1 A Join
(Select
A.f1,
A.f3 as first_f3,
B.f3 as second_f3
From
t2 A Join
t2 B ON
A.f1 = B.f1 And
A.f2 < B.f2 ANd
A.f3 <> B.f3
Where
DateDiff(DD, A.f2, B.f2) <5
) B On
A.f1 = B.f1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 29, 2008 at 12:59 pm
Thanks a lot Jack...with some little modifications i was able to get the required results
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply