top-down-top query approach

mubashir.surury

can anyone help me writing a query that starts from the outer query
selecting a few fields which are to be used in the nested subquery and
the result should be returned based on evaluation and the query should
again continue in the same way for the remaining records in the outer
sub query.

E.G
i have one table say XYZ and i have to choose a record say file-id from
this table having certain matching criteria say acct-id = 'abc' and
cent-id = 'def', i will fetch some other fields for it say start-date
and batch-no.
Now for the same file-id i have to check in the same table that no
record exist with say acct-id = 'pqr' and cent-id= 'mnp' for the same
start date and same batch no which have been fetched from the outer
subquery.
This process should continue for all the records in the outer sub
query.

Thanks in advance

Apr 7 '06 #1

situ

check this out
select a.file_id,a.start_date,a.batch_no
from xyz a
where a.acct_id='pqr' and a.cent_id='mnp'
and a.file_id not in
(select b.file_id from xyz b
where (b.acct_id<>a.acct_id) and (b.cent_id<>a.cent_id)
and (b.start_date=b.start_date) and (b.batch_no=a.batch_no))

Apr 7 '06 #2

mubashir.surury

hey situ;
thanx for ur prompt reply.i will try that out.

Apr 7 '06 #3

Tonkuma

Just write in SQL what you are writing in English.

SELECT file-id -- i have to choose a record say file-id from
this table
, start-date -- i will fetch some other fields for it say
start-date
, batch-no -- and batch-no
FROM XYZ A -- i have one table say XYZ
WHERE acct-id = 'abc' -- having certain matching criteria say
acct-id = 'abc'
and cent-id = 'def' -- and cent-id = 'def'
AND NOT EXISTS -- (i have to check in the same table that) no
record exist
(SELECT *
FROM XYZ B -- i have to check in the same table
WHERE b.file-id = a.file-id -- for the same file-id
AND acct-id = 'pqr' -- with say acct-id = 'pqr'
and cent-id = 'mnp' -- and cent-id= 'mnp'
AND b.start-date = a.start-date -- for the same start date
AND b.batch-no = a.batch-no -- and same batch no
)

Apr 7 '06 #4