select 2 or more rows with same id but..

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

SQL Server 2005 Forums

Transact-SQL (2005)

select 2 or more rows with same id but..

Author

Topic

magikminox
Starting Member

27 Posts

Posted - 2008-06-24 : 05:35:51

I want to select rows that have been amended or deleted and want to use the amend date as the condition.all amended rows have a flag of 1 and deleted ones have a flag of 2
i.e
select * from table
where log_changed > '2008-06-23' and log_changed < '2008-06-24'
will display all the rows that were amanded on the 2008-6-23 and with a flag of 1 or 2

but i also want the query to return the original row that was amended and has a flag of 0.the original row has a null log_changed field.

the rows that must be returned must be the ones amended or deleted ones on a specific date but with the original ones as well.

any ideas please

In god we trust,everything else we test.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-24 : 05:43:31

How is the original row related with amended row?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 05:53:06

may be this:-

select * from table t1
inner join table t2
on t2.pk=t1.pk
and t2.log_changed is null
and t2.flag=0
where t1.log_changed > '2008-06-23' and t1.log_changed < '2008-06-24'

pk is primary key

magikminox
Starting Member

27 Posts

Posted - 2008-06-24 : 05:55:40

they have the same id and everything.the only diference is the log flag of 0 for amended rows and 1 OR 2 for original or deleted rows.
hope that helps
id code amend date flag type
23 argi 2004-07-28 2 MATURED
23 argi 2004-07-29 1 MATURED
23 argi null 0 MATURED

In god we trust,everything else we test.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 05:57:52

select * from table t1
inner join table t2
on t2.id=t1.id
and t2.log_changed is null
and t2.flag=0
where t1.log_changed > '2008-06-23' and t1.log_changed < '2008-06-24'

magikminox
Starting Member

27 Posts

Posted - 2008-06-24 : 07:08:03

hie

the query above is only returning the row with a flag of 0
its not returning the other rows with flag 1 and 2.

In god we trust,everything else we test.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 07:11:30

quote:
Originally posted by magikminox

hie

the query above is only returning the row with a flag of 0
its not returning the other rows with flag 1 and 2.

In god we trust,everything else we test.

Can you illustrate what you want with some sample data from tables then?
The query i gave you lists all records with falg 0 and brings along with them modified ones with same id as seperate columns

magikminox
Starting Member

27 Posts

Posted - 2008-06-24 : 07:35:23

id code amend date flag type
23 argi 2008-06-28 2 MATURED
23 argi 2008-06-28 1 MATURED
23 argi null 0 MATURED
25 redd 2008-06-28 1 extra
25 redd null 0 extra

if the date was 2008-06-28 the query must return all the rows amended on that date and the originals

id code amend date flag type
23 argi 2008-06-28 1 MATURED
23 argi 2008-06-28 2 MATURED
23 hardi null 0 YIELD
25 redd 2008-06-28 1 extra
25 book null 0 extra

In god we trust,everything else we test.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 08:01:18

[code]select *
from
(select *,0 as Order from table t1
WHERE t1.flag in (1,2)
and t1.log_changed > '2008-06-28' and t1.log_changed < '2008-06-29'
union all
select t2.*,1 from table t1
inner join table t2
on t2.id=t1.id
and t2.log_changed is null
where t2.flag=0
and t1.log_changed > '2008-06-28' and t1.log_changed < '2008-06-29')t
order by t.id,t.Order[/code]
replace * with your actual column values

magikminox
Starting Member

27 Posts

Posted - 2008-06-24 : 08:32:08

thanks for the help
unfortunately i cant really follow up on that query and am getting a bit confused

In god we trust,everything else we test.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources