How do you do a select statement from another select statement

[gjconely]

This is what I need to do. This code can change based on what fields user selects, and embedded SQL can change also. I keep getting an access violation error. I cannot use Temporary tables in order to do this. Anyone?

select [book] from
{select book,page,doctype,[storey county dbase].docid from [storey county dbase]
inner join [doc_types] on [storey county dbase].docid=[doc_types].docid}
where [docid]='81673'


Greg Conely

http://www.ImageMentor.net

 

[MarkSweetland]

You can use subquerries if you want:

select a.[book] from
(
select book, docid=[storey county dbase].docid
from [storey county dbase]
inner join [doc_types]
on [storey county dbase].docid=[doc_types].docid
) as a
where a.[docid]='81673'

...but I think you don't need to:

select book
from [storey county dbase]
inner join [doc_types] on [storey county dbase].docid=[doc_types].docid
where [storey county dbase].docid='81673'



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[gjconely]

Thanks for the answer. I do have to do it this way though.


Greg Conely

http://www.ImageMentor.net

 

[gjconely]

The reason I have to - or believe that I have to - is:

- User will select a ODBC connection to use.
- User selects table\sql statement to query
- User selects field from above table\sql statement to query for a perticular value
- User selects what field from above table\sql statement to copy to another location.

So, from my first example, the SQL statement will usually be a table name, but sometimes they will have to do a SQL statement because they have to do a WHERE statement for a field that can only be found using the SQL statement, or they have to pull information from a field where only another field equals another value, etc...

If you want to see what it is for, look at

http://www.imagementor.net/freetrial_details.asp?Appid=24

I am in the process of updating the content for this application thanks to your help. But it should give you an idea of what I have to do.

Greg Conely

http://www.ImageMentor.net