SQL Statement

[John Smith]

Can you do a Select Statement within a Select Statement? I want to build a
query similar to queries built in Access which link to other queries but
using only SQL Statements. Is it possible? If so, how do you do it?

Thanx in adv.

[Alvin Bruney [MVP]]

yes, it's called subqueries but this is not the forum for this. thy the ado
newsgroup

--
Regards,
Alvin Bruney
[ASP.NET MVP http://mvp.support.microsoft.com/default.aspx]
Got tidbits? Get it here... http://tinyurl.com/27cok
"John Smith" <no*******@addr ess.com> wrote in message
news:uu******** ********@TK2MSF TNGP10.phx.gbl. ..

Can you do a Select Statement within a Select Statement? I want to build
a
query similar to queries built in Access which link to other queries but
using only SQL Statements. Is it possible? If so, how do you do it?

Thanx in adv.

[Murphy]

In Access you can view the SQL statement behind the query designer.

"John Smith" <no*******@addr ess.com> wrote in message
news:uu******** ********@TK2MSF TNGP10.phx.gbl. ..

Can you do a Select Statement within a Select Statement? I want to build a query similar to queries built in Access which link to other queries but
using only SQL Statements. Is it possible? If so, how do you do it?

Thanx in adv.

[Scott Allen]

You want to create a "derived table" by using a select in the FROM
clause.

--
Scott
http://www.OdeToCode.com

On Thu, 22 Apr 2004 17:37:46 -0400, "John Smith"
<no*******@addr ess.com> wrote:

Can you do a Select Statement within a Select Statement? I want to build a
query similar to queries built in Access which link to other queries but
using only SQL Statements. Is it possible? If so, how do you do it?

Thanx in adv.

[Miha Markic [MVP C#]]

Or in the SELECT part :)

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"Scott Allen" <bitmask@[nospam].fred.net> wrote in message
news:re******** *************** *********@4ax.c om...

You want to create a "derived table" by using a select in the FROM
clause.

--
Scott
http://www.OdeToCode.com

On Thu, 22 Apr 2004 17:37:46 -0400, "John Smith"
<no*******@addr ess.com> wrote:

Can you do a Select Statement within a Select Statement? I want to build aquery similar to queries built in Access which link to other queries but
using only SQL Statements. Is it possible? If so, how do you do it?

Thanx in adv.

[Miha Markic [MVP C#]]

Hi John,

What database are you talking about?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"John Smith" <no*******@addr ess.com> wrote in message
news:uu******** ********@TK2MSF TNGP10.phx.gbl. ..

Can you do a Select Statement within a Select Statement? I want to build a query similar to queries built in Access which link to other queries but
using only SQL Statements. Is it possible? If so, how do you do it?

Thanx in adv.

[John Smith]

I am currently using Access, but if I migrated to another db I would like to
be able to easily take my queries with me in one big sql statement if
possible. I do know about the SQL View within Access, but I was wondering
for future use and for knowledge if it is possible to have queries within
queries, but only using the sql statement of the queries.

Maybe something like : Select tblOne.ID, [Select Count(tblTwo.Se condaryID)
FROM tblTwo WHERE tblTwo.Secondar yID = tblOne.ID], [Select
Count(tblTwo.An otherID) FROM tblTwo WHERE tblTwo.Secondar yID = tblOne.ID]
From tblOne.ID

I have no idea how to do this or if it is even possible or if the above is
even correct, so I thought that I would check here. I apologize for not
using the ADO.NET newgroups and I thank you for the responses I have already
received. If anybody could just give me some examples or some hints or
websites to check out I would definitely appreciate the help.

Thanx a lot for your help.
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:OV******** ******@TK2MSFTN GP11.phx.gbl...

Hi John,

What database are you talking about?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"John Smith" <no*******@addr ess.com> wrote in message
news:uu******** ********@TK2MSF TNGP10.phx.gbl. ..

Can you do a Select Statement within a Select Statement? I want to

build a

query similar to queries built in Access which link to other queries but
using only SQL Statements. Is it possible? If so, how do you do it?

Thanx in adv.

[John Smith]

"Derived Tables" Thanx Scott Allen
"John Smith" <no*******@addr ess.com> wrote in message
news:uu******** ********@TK2MSF TNGP10.phx.gbl. ..

Can you do a Select Statement within a Select Statement? I want to build a query similar to queries built in Access which link to other queries but
using only SQL Statements. Is it possible? If so, how do you do it?

Thanx in adv.

[Miha Markic [MVP C#]]

Hi John,

Yes, of course it is possible.
Use normal brackets () instead of square ones and make sure that select
returns only one record.
But I guess it really depends on database if it supports the notation
(Access 2003, Sql server 2000 and Oracle 9ir2 do).
--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"John Smith" <no*******@addr ess.com> wrote in message
news:%2******** *******@TK2MSFT NGP11.phx.gbl.. .

I am currently using Access, but if I migrated to another db I would like to be able to easily take my queries with me in one big sql statement if
possible. I do know about the SQL View within Access, but I was wondering
for future use and for knowledge if it is possible to have queries within
queries, but only using the sql statement of the queries.

Maybe something like : Select tblOne.ID, [Select Count(tblTwo.Se condaryID)
FROM tblTwo WHERE tblTwo.Secondar yID = tblOne.ID], [Select
Count(tblTwo.An otherID) FROM tblTwo WHERE tblTwo.Secondar yID = tblOne.ID]
From tblOne.ID

I have no idea how to do this or if it is even possible or if the above is
even correct, so I thought that I would check here. I apologize for not
using the ADO.NET newgroups and I thank you for the responses I have already received. If anybody could just give me some examples or some hints or
websites to check out I would definitely appreciate the help.

Thanx a lot for your help.
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:OV******** ******@TK2MSFTN GP11.phx.gbl...

Hi John,

What database are you talking about?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"John Smith" <no*******@addr ess.com> wrote in message
news:uu******** ********@TK2MSF TNGP10.phx.gbl. ..

Can you do a Select Statement within a Select Statement? I want to

build

a

query similar to queries built in Access which link to other queries but using only SQL Statements. Is it possible? If so, how do you do it?

Thanx in adv.

[John Smith]

Thanx a lot for all the help. It works perfectly.

Okay, so let me ask you one more question now (last question, I promise
;) ). Can I connect to another database through the derived table method
using SQL for Access 2000? If so, how would I go about adding a new db
connection string into the SQL Statement? Both databases are in Access
2000.

SELECT TableOne.Field1 , (SELECT TableTwo.Field2 FROM TableTwo WHERE
TableTwo.Field1 = TableOne.Field1 ) AS FieldDescriptio n FROM TableOne
WHERE....
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:OQ******** ******@TK2MSFTN GP09.phx.gbl...

Hi John,

Yes, of course it is possible.
Use normal brackets () instead of square ones and make sure that select
returns only one record.
But I guess it really depends on database if it supports the notation
(Access 2003, Sql server 2000 and Oracle 9ir2 do).
--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"John Smith" <no*******@addr ess.com> wrote in message
news:%2******** *******@TK2MSFT NGP11.phx.gbl.. .

I am currently using Access, but if I migrated to another db I would like

to

be able to easily take my queries with me in one big sql statement if
possible. I do know about the SQL View within Access, but I was wondering for future use and for knowledge if it is possible to have queries within queries, but only using the sql statement of the queries.

Maybe something like : Select tblOne.ID, [Select Count(tblTwo.Se condaryID) FROM tblTwo WHERE tblTwo.Secondar yID = tblOne.ID], [Select
Count(tblTwo.An otherID) FROM tblTwo WHERE tblTwo.Secondar yID = tblOne.ID] From tblOne.ID

I have no idea how to do this or if it is even possible or if the above is even correct, so I thought that I would check here. I apologize for not
using the ADO.NET newgroups and I thank you for the responses I have

already

received. If anybody could just give me some examples or some hints or
websites to check out I would definitely appreciate the help.

Thanx a lot for your help.
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:OV******** ******@TK2MSFTN GP11.phx.gbl...

Hi John,

What database are you talking about?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development miha at rthand com
www.rthand.com

"John Smith" <no*******@addr ess.com> wrote in message
news:uu******** ********@TK2MSF TNGP10.phx.gbl. ..
> Can you do a Select Statement within a Select Statement? I want to

build

a
> query similar to queries built in Access which link to other queries

but > using only SQL Statements. Is it possible? If so, how do you do it? >
> Thanx in adv.
>
>