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

 

[Murphy]

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

 

[Scott Allen]

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

 

[Miha Markic [MVP C#]]

Or in the SELECT part

 

[Miha Markic [MVP C#]]

Hi John,

What database are you talking about?

 

[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.SecondaryID)
FROM tblTwo WHERE tblTwo.SecondaryID = tblOne.ID], [Select
Count(tblTwo.AnotherID) FROM tblTwo WHERE tblTwo.SecondaryID = 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.

Hi John,

What database are you talking about?

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

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

 

[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

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.SecondaryID)
FROM tblTwo WHERE tblTwo.SecondaryID = tblOne.ID], [Select
Count(tblTwo.AnotherID) FROM tblTwo WHERE tblTwo.SecondaryID = 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.

Hi John,

What database are you talking about?

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

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 FieldDescription FROM TableOne
WHERE....

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

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.SecondaryID)
FROM tblTwo WHERE tblTwo.SecondaryID = tblOne.ID], [Select
Count(tblTwo.AnotherID) FROM tblTwo WHERE tblTwo.SecondaryID = 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.

Hi John,

What database are you talking about?

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

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 figured out my problem. I used the IN Clause to accomplish this. Here is
a sample for those of you wanting to know how.

SELECT tTable1.Field1, tTable1.Field2, (SELECT tTable2.Field2 FROM tTable2
IN 'C:\mydatabaselocation.mdb' WHERE tTable2.Field1=tTable1.Field1 AS
MyDataFromTable2
FROM tTable1;

Thanx.

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 FieldDescription FROM TableOne
WHERE....

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

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.SecondaryID)
FROM tblTwo WHERE tblTwo.SecondaryID = tblOne.ID], [Select
Count(tblTwo.AnotherID) FROM tblTwo WHERE tblTwo.SecondaryID = 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
Hi John,

What database are you talking about?

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

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.