Sub Query chaos - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Sub Query chaos

Author

Topic

moneyman
Starting Member

6 Posts

Posted - 2006-02-22 : 21:15:42

I am appealing to the sql gods out there. I have been trying to figure this out for a couple of days. And I am about to lose it.

So let me set this up.
tablex
id(int), source(varchar), destination(varchar), start(datetime), finish(datetime), success(bit)

my data looks like this
1, c:\a.xxx, d:\a.xxx, 2/20/06, 2/20/06, 0
2, d:\a.xxx, e:\a.xxx, null, null, null
3, d:\a.xxx, f:\a.xxx, null, null, null
4, c:\a.xxx, d:\a.xxx, null, null, null
...

here's my query

select id, source, destination
from tablex
where start is null
and not exists ( select id from tablex y where y.success=1 and y.destination=x.source)

unfortunately, that's not going to work. what i want is
1. if success=0 for id=1, i don't want id=2 and 3 to show up.
2. if success=1 for id=1, i want id=2,3,4,... to show up
3. if success=0 for id=1 and success=1 for id=4, i want id=2,3,... to show up

is this doable at all? i really need your help. thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-22 : 21:56:35

Don't quite understand your requirement. Can you explain more with more sample data and the expected result ?

----------------------------------
'KH'
It is inevitable

moneyman
Starting Member

6 Posts

Posted - 2006-02-22 : 22:18:42

my sample data looks like

scenario 1
1, c:\b.xxx, d:\b.xxx, 2/20/06, 2/20/06, 1
2, c:\a.xxx, d:\a.xxx, 2/20/06, 2/20/06, 0
3, d:\a.xxx, e:\a.xxx, null, null, null
4, d:\a.xxx, f:\a.xxx, null, null, null
5, c:\a.xxx, d:\a.xxx, null, null, null
6, d:\b.xxx, e:\b.xxx, null, null, null

The expected result should be
5, c:\a.xxx, d:\a.xxx, null, null, null
6, d:\b.xxx, e:\b.xxx, null, null, null

because id=2 has success=0

scenario 2
1, c:\b.xxx, d:\b.xxx, 2/20/06, 2/20/06, 1
2, c:\a.xxx, d:\a.xxx, 2/20/06, 2/20/06, 1
3, d:\a.xxx, e:\a.xxx, null, null, null
4, d:\a.xxx, f:\a.xxx, null, null, null
5, c:\a.xxx, d:\a.xxx, null, null, null
6, d:\b.xxx, e:\b.xxx, null, null, null

the expected result should be
3, d:\a.xxx, e:\a.xxx, null, null, null
4, d:\a.xxx, f:\a.xxx, null, null, null
5, c:\a.xxx, d:\a.xxx, null, null, null
6, d:\b.xxx, e:\b.xxx, null, null, null

scenario 3
1, c:\b.xxx, d:\b.xxx, 2/20/06, 2/20/06, 1
2, c:\a.xxx, d:\a.xxx, 2/20/06, 2/20/06, 0
3, d:\a.xxx, e:\a.xxx, null, null, null
4, d:\a.xxx, f:\a.xxx, null, null, null
5, c:\a.xxx, d:\a.xxx, 2/20/06, 2/20/06, 1
6, d:\b.xxx, e:\b.xxx, null, null, null

the expected result should be
3, d:\a.xxx, e:\a.xxx, null, null, null
4, d:\a.xxx, f:\a.xxx, null, null, null
6, d:\b.xxx, e:\b.xxx, null, null, null

let me know if this still does not make any sense.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-22 : 22:39:47

yup. Still does not make sense to me. Can you explain your overall requirement and for each scenario, how the expected result comes about ?
As for scenario 2 & 3, it seems like it is just

where start is null

----------------------------------
'KH'
It is inevitable

moneyman
Starting Member

6 Posts

Posted - 2006-02-22 : 23:43:12

Sorry for the confusion. Let me try again. The requirement is to copy the source file (source column) to the destination (destination column).

in the case of scenario 1, i should not be able to process task id = 3 and 4 because it failed copying c:\a.xxx to d:\a.xxx (success=0). therefore, the next available task should be task id=5. that is to try copying it again. and once it succeeds (success=1), i should now be able to process task id = 3 and then 4. (see case scenario 3)

i should not be able to proceed processing file a.xxx as soon as it successfully copies it to d:\a.xxx. and once it is, i should be able to copy file a.xxx anywhere.

if i just put a clause

where start is null

it would process task id=3 and 4 regardless whether task id 2 is successful or not. that's not what i need. i should get task id =3 and 4 in the result set if and only if task id = 2 is successful.

hope this is clear now.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-23 : 00:46:32

[code]create table #scenario1
(
id int,
source varchar(20),
destination varchar(20),
start datetime,
finish datetime,
success bit
)

create table #scenario2
(
id int,
source varchar(20),
destination varchar(20),
start datetime,
finish datetime,
success bit
)

create table #scenario3
(
id int,
source varchar(20),
destination varchar(20),
start datetime,
finish datetime,
success bit
)

insert into #scenario1
select 1, 'c:\b.xxx', 'd:\b.xxx', '2006-02-20', '2006-02-20', 1 union all
select 2, 'c:\a.xxx', 'd:\a.xxx', '2006-02-20', '2006-02-20', 0 union all
select 3, 'd:\a.xxx', 'e:\a.xxx', null, null, null union all
select 4, 'd:\a.xxx', 'f:\a.xxx', null, null, null union all
select 5, 'c:\a.xxx', 'd:\a.xxx', null, null, null union all
select 6, 'd:\b.xxx', 'e:\b.xxx', null, null, null

--The expected result should be
--5, c:\a.xxx, d:\a.xxx, null, null, null
--6, d:\b.xxx, e:\b.xxx, null, null, null
--because id=2 has success=0

select *
from #scenario1 x
where start is null
and (
not exists (select * from #scenario1 y where y.success = 0 and y.destination = x.source)
or exists (select * from #scenario1 y where y.success = 1 and y.destination = x.source)
)

insert into #scenario2
select 1, 'c:\b.xxx', 'd:\b.xxx', '2006-02-20', '2006-02-20', 1 union all
select 2, 'c:\a.xxx', 'd:\a.xxx', '2006-02-20', '2006-02-20', 1 union all
select 3, 'd:\a.xxx', 'e:\a.xxx', null, null, null union all
select 4, 'd:\a.xxx', 'f:\a.xxx', null, null, null union all
select 5, 'c:\a.xxx', 'd:\a.xxx', null, null, null union all
select 6, 'd:\b.xxx', 'e:\b.xxx', null, null, null

--the expected result should be
--3, d:\a.xxx, e:\a.xxx, null, null, null
--4, d:\a.xxx, f:\a.xxx, null, null, null
--5, c:\a.xxx, d:\a.xxx, null, null, null
--6, d:\b.xxx, e:\b.xxx, null, null, null

select *
from #scenario2 x
where start is null
and (
not exists (select * from #scenario2 y where y.success = 0 and y.destination = x.source)
or exists (select * from #scenario2 y where y.success = 1 and y.destination = x.source)
)

insert into #scenario3
select 1, 'c:\b.xxx', 'd:\b.xxx', '2006-02-20', '2006-02-20', 1 union all
select 2, 'c:\a.xxx', 'd:\a.xxx', '2006-02-20', '2006-02-20', 0 union all
select 3, 'd:\a.xxx', 'e:\a.xxx', null, null, null union all
select 4, 'd:\a.xxx', 'f:\a.xxx', null, null, null union all
select 5, 'c:\a.xxx', 'd:\a.xxx', '2006-02-20', '2006-02-20', 1 union all
select 6, 'd:\b.xxx', 'e:\b.xxx', null, null, null

--the expected result should be
--3, d:\a.xxx, e:\a.xxx, null, null, null
--4, d:\a.xxx, f:\a.xxx, null, null, null
--6, d:\b.xxx, e:\b.xxx, null, null, null

select *
from #scenario3 x
where start is null
and (
not exists (select * from #scenario3 y where y.success = 0 and y.destination = x.source)
or exists (select * from #scenario3 y where y.success = 1 and y.destination = x.source)
)[/code]

----------------------------------
'KH'
It is inevitable

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-23 : 03:19:22

damn KH, that was a bit of work!

moneyman
Starting Member

6 Posts

Posted - 2006-02-23 : 14:45:25

You ROCK, KH!!!

Thanks for your help.

moneyman
Starting Member

6 Posts

Posted - 2006-02-23 : 16:56:53

Hey KH,

I ran your query to my sample data and it worked like expected. However, there is one particular scenario that the query did not work as expected.

that is if I have

1, c:\b.xxx, d:\b.xxx, 2/20/06, 2/20/06, 1
2, c:\a.xxx, d:\a.xxx, 2/20/06, 2/20/06, 1
3, c:\a.xxx, d:\a.xxx, 2/20/06, 2/20/06, 0
4, d:\a.xxx, f:\a.xxx, null, null, null
5, d:\a.xxx, e:\a.xxx, null, null, null
6, d:\b.xxx, e:\b.xxx, null, null, null

task id = 4 and 5 shows up. The expected result that I want should be that task id = 6 will be the next task to be process. Because it successfully copied the file a.xxx from c to d on task id = 2 but then failed when it tried to copy the same file from c to d.

Argh...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-23 : 20:29:52

The occurance sequence of task is it identify by the column id ?
So for this scenario
scenario 3
1, c:\b.xxx, d:\b.xxx, 2/20/06, 2/20/06, 1
2, c:\a.xxx, d:\a.xxx, 2/20/06, 2/20/06, 0
3, d:\a.xxx, e:\a.xxx, null, null, null
4, d:\a.xxx, f:\a.xxx, null, null, null
5, c:\a.xxx, d:\a.xxx, 2/20/06, 2/20/06, 1
6, d:\b.xxx, e:\b.xxx, null, null, null

the next task to be process should be only 6 ? Task 3 & 4 is out because of task 2 failed ?

----------------------------------
'KH'
It is inevitable

moneyman
Starting Member

6 Posts

Posted - 2006-02-24 : 01:37:11

The occurance sequence is by column id. So for scenario 3, since task id=2 failed, it couldn't do 3 and 4 at first. Therefore, the next task was task id = 5. And so when we query the table again, it should find task 3 and 4 first before task id = 6 because of task id = 5 was successful.

In the fourth scenario, even though task id = 2 was successful copying a.xxx to d, it should pass task id = 4 and 5 because task id = 3 failed when we try to copy the same filename to d again.

Since this is going to be run on a stored procedure, maybe we can perhaps store the id, destination, and success of the last task that is identical to the source of the next task. And if the success=1, then process it. Otherwise, we can skip it until we find one where the destination is not the same as the source.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-24 : 03:17:10

This should work.

select	*
from	#scenario x
where	start	is null
and	isnull((select top 1 success from #scenario y 
                 where y.destination = x.source 
                 and   y.id          <> x.id 
                 and   success is not null 
                 order by id desc), 1) = 1

----------------------------------
'KH'
It is inevitable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources