Are sub-subqueries possible?

[orin@twjc.co.uk]

Hi all,

I've seen mention that you can use nested subqueries down to as many
levels as you like but whenever I run the following:

select * from table1
where tab1ID in
(select tab1ID from table2 where tab2ID in
(select tab2ID from Table3 where Tab3ID=N))

I get the error "incorrect syntax near the keyword 'where'"

Can anyone confirm that sub-subqueries are possible? If so is the
syntax I'm using correct?

I'm running SQL 2000 through a Delphi 5 app.

Regards
Jon

[SQL]

Re: Are sub-subqueries possible?

yes look at this example in the pubs DB

use pubs
select * from authors
where au_id in(select au_id from authors where au_id in( select au_id
from authors where au_id ='172-32-1176'))



You can also write your query as follows
select t1.* from table1 t1
join table2 on t1.tab1ID =t2.tab1ID
join Table3 t3 on t3.tab2ID =t2.tab2ID
where t3.Tab3ID=N --this should be 'N' or a number probably ???


Denis the SQL Menace

http://sqlservercode.blogspot.com/

[orin@twjc.co.uk]

Re: Are sub-subqueries possible?

Thanks Denis

yeah I just installed Query Analyser & ran my query through that & it
worked fine so it must be a limitation of the Delphi TQuery component.

Oh well.

I'll try a join as you suggested instead.

Thanks for your time
Jon