By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,589 Members | 2,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,589 IT Pros & Developers. It's quick & easy.

top-down-top query approach

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
hey situ;
thanx for ur prompt reply.i will try that out.

Apr 7 '06 #3

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.