Selecting from a table identified by the result of another query???

I want to select a row from one of a number of tables based on the result of a query on one table. This is a bit hard to explain properly, so I'll try a diagram:

Table1

TABLE		PK	....
Fred		3	....
Banana		2	....

Table Fred			Table Banana
PK	...			PK	...
1	...			1	...
2	...			2	...
3	...			3	...
4	...			4	...

Now I want to do a select like:

select * from (select TABLE from Table1 where...)

        where PK = (select PK from Table1 where...);

Is it possible to do this type of thing? I know that each table has an object number in sys.obj$ which I could look up - would that help at all? Is there another way around this problem?

Please reply by email, as my newsfeed is hopeless.

Thanks

---

Gideon King                    | Phone +64-3-479 8347
University of Otago            | Fax   +64-3-479 8529
Computer Science Applied       | e-mail gideon_at_csarc.otago.ac.nz
         Research Centre       | NeXT mail, MIME ok. PGP key available.
Department of Computer Science |
P.O. Box 56                    | Excuse me for butting in,
Dunedin                        |      but I'm interrupt-driven.
New Zealand                    |
WWW access:  
http://www.csarc.otago.ac.nz:805/PersonalHomePages/Gideon.htmld/