January 19, 2010 at 9:55 pm
Hi,
The inner join in sql query is returning multiple records with one which has date and one which doesn't varies between two records,how can i get only the record which has date in between these two
SELECT distinct dbo.table1.Value1, dbo.table2.Value2,dbo.table3.Date,dbo.table3.Value6
FROM dbo.table1
INNER JOIN table3 ON dbo.table1.Value1 = dbo.table3.Value1
INNER JOIN table2 ON dbo.table1.SecID= dbo.table2.ID
Right now i am getting two values one with out date and one with date
Thanks
January 19, 2010 at 10:13 pm
if you can post some sample data, it would help.
but to answer your question, a'where date is not null' should get what you are looking for
January 19, 2010 at 10:43 pm
Hi there,
Thanks for the reply,but i also need records which doesnt have date set
For Example:
Value1 Value2 Date Value3
a1 S9 NULL M1
b1 S3 NULL B1
b1 S3 2010-01-27 00:00:00.000 B1
c1 S4 NULL B1
c1 S4 2010-02-04 00:00:00.000 B1
For example from the above if add date is not null then it will exclude a1 record from the list which is needed,also where dates set i would only like to pick the one with date set instead of null one
b1 S3 NULL B1
b1 S3 2010-01-27 00:00:00.000 B1
Deleting the first one and using the second from the above
Thanks
January 19, 2010 at 10:53 pm
Hi again,
i inserted the data as
select * into test2 from (
select 'a1' Value, 'S9' Value2,NULL Date, 'M1' as Value3
union all
select 'b1' ,'S3' ,NULL ,'B1'
union all
select 'b1' ,'S3' ,'2010-01-27 00:00:00.000', 'B1'
union all
select 'c1' ,'S4' ,NULL, 'B1'
union all
select 'c1' ,'S4' ,'2010-02-04 00:00:00.000', 'B1'
)a
and i THINK you are looking for this statement.
select value, value2, MAX(date), value3 from test2 group by value, value2, value3
January 19, 2010 at 10:58 pm
Yes thank you,that works
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply