Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.Is this what you want?
select * from (select * from table1) as table2
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>|||You can use derived queries:
select col1
, col2
from ( select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:

> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>
>|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.
Is this what you want?
select * from (select * from table1) as table2
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>
|||You can use derived queries:
select col1
, col2
from (select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:

> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way to
> get this to run properly?
> Thanks.
>
>
|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>
|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye
|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.Is this what you want?
select * from (select * from table1) as table2
--
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>|||You can use derived queries:
select col1
, col2
from ( select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way to
> get this to run properly?
> Thanks.
>
>|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple Select Statements in 1 Query

i'm going to guess that there's no way to do this in T-SQL (or any
other SQL implementation), but I figured I'd ask around:
basically, what I'm looking to do is replace this union statement::
SELECT intX
FROM Table1
UNION
SELECT intY
FROM Table1
with a syntactical shortcut:
SELECT intX
SELECT intY
FROM Table1
The need for this shortcut is that my FROM clause is very complex, and
copying it multiple times is messy (and frankly, I'm in no mood to
create a View given the number of non-unique column names in the joined
tables). Anyone have any suggestions?scottstein@.gmail.com wrote:
> i'm going to guess that there's no way to do this in T-SQL (or any
> other SQL implementation), but I figured I'd ask around:
> basically, what I'm looking to do is replace this union statement::
> SELECT intX
> FROM Table1
> UNION
> SELECT intY
> FROM Table1
> with a syntactical shortcut:
> SELECT intX
> SELECT intY
> FROM Table1
> The need for this shortcut is that my FROM clause is very complex, and
> copying it multiple times is messy (and frankly, I'm in no mood to
> create a View given the number of non-unique column names in the joined
> tables). Anyone have any suggestions?
In SQL Server 2005 you can use a CTE. You'll have to assign some unique
column names though:
WITH T1 (intx, inty)
AS
(SELECT ... /* your complex logic here */ )
SELECT intx
FROM T1
UNION
SELECT inty
FROM T1 ;
There may be a better solution by changing the complex part of your
query that you didn't post.
David Portas
SQL Server MVP
--|||if are aren't on 2K5 yet, you can cross join with a sequence table,
like this:
select case when t=1 then expr1 else expr2 end
from(
-- your really complex expression goes here
select 'expr1' expr1, 'expr2' expr2)c
cross join
(select 1 t union all select 2) t
expr1
expr2
(2 row(s) affected)|||Thanks David + Alexander, I appreciate your responses. Unfortunately,
CTE has the same problem as the view -- unique column names. I haven't
even seen 2005 yet, so that isn't an option for now. Again, this
problem isn't unbearable, but it just makes my code a little messy.

Multiple Select statements + Stored Procedure

Hi all,

I have 2 select statements in my Stored Proc.

I want to display the results of each query in my DataGridView.

However, only the data of the last select query is returned.

Why is this?

Thanks.

At a time you can only bind one resultset. if you need both the resultset on your page, you need to have 2 datagrid view.

If you use dataset,

grid1.DataSource = dataset.Tables[0];

grid2.DataSource = dataset.Tables[1];

If you use datareader,

grid1.DataSource = datareader;

datareader.NextResultSet();

grid2.DataSource = datareader;

|||

Ideally I want all data displayed in one DataGridView.

Each SELECT Query returns the exact same Columns but differing data.

|||

As both queries return the same columns, could you use a union clause in your stored proc?

eg

Code Snippet

CREATE PROC proc

AS

SELECT col1, col2

FROM table1

UNION ALL
SELECT col1, col2

FROM table2

This would mean you'd have just one results set.

If its more complicated than that, you could put the results of each query into a temp table/variable and just select from that?

HTH!

|||

May be something like this...

Select 'Table 1' as Source, * From FirstTable

Union ALL

Select 'Table 2' as Source, * From SecondTable

Order By

Source, ....

|||If there any relation between the tables,you can group them in the gridview|||

Thanks!!

UNION ALL works.

multiple select statements

Hi guys and gals,

I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:

'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'

My Code is below:

//Start of sql

CREATE PROCEDURE ADMIN_GetSingleUsers
(
@.userID int
)
AS

DECLARE @.userSQL int
SET @.userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
Users.Unit_ID As usersUnitID,
*
From TITLE, Users
WHERE
User_ID = @.userID AND
TITLE.TITLE_ID = Users.Title_ID )

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

//End of sql

Can you point to what I am doing wrong? Thanks in advance!

You are trying to SET @.userSQL to more than one value (User_ID, Title.Title, Users.Active, Users.Approved, and Users.Unit_ID).

Try it in one statement instead, something like this:

SELECT
Units.Unit_ID,
Units.Parent_ID,
Units.Unit_Name
FROM
Units
INNER JOIN
Users ON Units.Unit_ID = Users.Unit_ID AND Users.User_ID = @.UserID
INNER JOIN
Title ON Users.Title_ID = Title.Title_ID

|||

Depends on if you wanted both result sets to be returned or just the second one.

return both:

SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
@.userSQL=Users.Unit_ID As usersUnitID,
*
From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

return second one:

SELECT @.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

Or if you don't need @.userSQL except for limiting the second query:

SELECT Unit_ID, Parent_ID, Unit_Name@.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

JOIN UNITS ON (Users.Unit_ID=UNITS.Unit_ID)

WHERE User_ID = @.userID

|||

Another Question.

Is there a way to do the following?

DECLARE unitID nVarChar(255)
SET @.unitID = (Select * From Units)

and give @.unitID the exact value from *?
Or loop throug the @.unitID and get the value I need?

|||Declare it as a table?

multiple select statement in a stored-proc?

I am using SQL sever 2k and C#.

There is a stored-procedure that it has multiple select statements as returned result set. How can I use SqlCommand.ExecuteReader to get all result set?

What if the multiple select statements is " FOR XML", how can I set all xml using ExecuteXmlReader?

I tried to use ExecuteReader or ExecuteXmlReader, but seems that I can only get back the result set of the first select statement, all others are messed up.

stored procedure example: NorthWind database:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE dbo.getShippersAndEmployeesXML
AS
select * from Shippers for xml auto, elements
select * from Employees for xml auto, elements
RETURN @.@.ERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

C# code example:

//set connect, build sqlcommand etc
XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
while(reader.Read()) sb.Append(reader.ReadOuterXml());

Thanks for your help.

reader.NextResult()

|||it works for SqlDataReader. however XmlTextReader seems don't have this method. anyway I can just use SqlDataReader.
thanks very much.