Question regarding SP

Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Hello Patrick,
Inside of T-SQL if you want to get the results back from a stored procedure
you need to insert them into a table as part of running the query.
The syntax for this is INSERT <<table>> (<<cols>> ) EXECUTE <<procedure>>
So for example
INSERT myTable ([Row1], [Row1]) EXECUTE myProcedure
The table you insert these records into can be a temp table, table var, or
even a normal table. Once you have your results in a table you can then crea
te
a cursor using that table.
Aaron Weiker
http://aaronweiker.com/

> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows.
> I want to define cursor on the result which is sent by stored
> procedure.
> How can i do that ?
> Thanks
>|||Seeing as you are asking this in the DTS group, amongst others, you may wan
t to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl.
.
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows
. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>

Question regarding SP

Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
Thanks
Patrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com
|||Seeing as you are asking this in the DTS group, amongst others, you may want to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>
|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com
|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>
|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>
|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>

Question regarding SP

Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Seeing as you are asking this in the DTS group, amongst others, you may wan
t to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl..
.
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows
. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>

Question regarding SP

Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Seeing as you are asking this in the DTS group, amongst others, you may want to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
>> How do you mean? You want to use a cursor from the application? Or are
>> you saying you want to use the results from one stored procedure in
>> another stored procedure? Or are you saying you want to define a cursor
>> on the results from within the procedure so you can perform row-by-row
>> processing? Or are you saying something else altogether?
>> Please provide some details about what you are doing. Cursors on SQL
>> Server are very slow and there may be other set-based solutions you can
>> use.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
>> How do you mean? You want to use a cursor from the application? Or are
>> you saying you want to use the results from one stored procedure in
>> another stored procedure? Or are you saying you want to define a cursor
>> on the results from within the procedure so you can perform row-by-row
>> processing? Or are you saying something else altogether?
>> Please provide some details about what you are doing. Cursors on SQL
>> Server are very slow and there may be other set-based solutions you can
>> use.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeIf you are populating the table with just one row, then use a table variable
instead.
AMB
"joseph.fanelli@.vba.va.gov" wrote:

> I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||If you are only populating with one row then chances are you can do this
without a temp table. But to answer your question no others can not see
this. But you might want to do as Alejandro says and use a table variable
instead.
Andrew J. Kelly SQL MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844980.325249.265010@.l41g2000cwc.googlegroups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||Why even use a temporary table?
Declare variables, populate them, then return a recorset:
select @.variable1 as ..., @.variable2 as ... @.variable3 as ... etc.
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844980.325249.265010@.l41g2000cwc.googlegroups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||Andrew,
I did not think about it, but yes, better to use an output parameter. What
about a connection taken from the pool, if the sp does not drop the temporar
y
table explicitly, and uses this code to create it:
if object_id('tempdb..#temptbl') is null
create table #temptbl ...
...
then next time the sp is executed using the same connection, a row inserted
by previous execution can be selected, correct?
AMB
"Andrew J. Kelly" wrote:

> If you are only populating with one row then chances are you can do this
> without a temp table. But to answer your question no others can not see
> this. But you might want to do as Alejandro says and use a table variable
> instead.
> --
> Andrew J. Kelly SQL MVP
>
> <joseph.fanelli@.vba.va.gov> wrote in message
> news:1113844980.325249.265010@.l41g2000cwc.googlegroups.com...
>
>|||If they are using connection pooling and follow the proper procedures a
sp_resetconnection is called that will clean up any such things and put the
connection settings back to the proper settings. Normally a temp table
created in a sp will go out of scope when the sp is completed as well. It
is only if they created the temp table outside of a sp that it will hang
around.
Andrew J. Kelly SQL MVP
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:8C7B0F8B-12B7-49F0-818B-F39C2415C8FE@.microsoft.com...
> Andrew,
> I did not think about it, but yes, better to use an output parameter. What
> about a connection taken from the pool, if the sp does not drop the
> temporary
> table explicitly, and uses this code to create it:
> if object_id('tempdb..#temptbl') is null
> create table #temptbl ...
> ...
> then next time the sp is executed using the same connection, a row
> inserted
> by previous execution can be selected, correct?
>
> AMB
> "Andrew J. Kelly" wrote:
>|||Andrew,
You are right, the temporary table goes out of scope when the sp has
finished. I wonder what kind of cleaning is done by sp_resetconnection, I ca
n
not find anything about it in the BOL.
Thanks,
AMB
"Andrew J. Kelly" wrote:

> If they are using connection pooling and follow the proper procedures a
> sp_resetconnection is called that will clean up any such things and put th
e
> connection settings back to the proper settings. Normally a temp table
> created in a sp will go out of scope when the sp is completed as well. It
> is only if they created the temp table outside of a sp that it will hang
> around.
> --
> Andrew J. Kelly SQL MVP
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:8C7B0F8B-12B7-49F0-818B-F39C2415C8FE@.microsoft.com...
>
>

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
Joe
The #tablename temp table exists only for the connection that calls the
proc. You are safe to call the same proc and the same table name as many
times as necessary without fear. Do not however use ##{tablename}. This will
cause a global temp table to be created and is referenceable by other
connections until the connection that created it is dropped.
--Rick Butler
"joseph.fanelli@.vba.va.gov" wrote:

> I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>
|||Why not just select that one row and avoid creating a temp table? Improper
use of temporary objects in stored procedures can cause recompiles and
affect SQL performance. See
http://support.microsoft.com/default...b;en-us;243586 for details.
Adrian
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BC656DA7-7BB2-4527-94D5-BCD53D702DE4@.microsoft.com...[vbcol=seagreen]
> The #tablename temp table exists only for the connection that calls the
> proc. You are safe to call the same proc and the same table name as many
> times as necessary without fear. Do not however use ##{tablename}. This
> will
> cause a global temp table to be created and is referenceable by other
> connections until the connection that created it is dropped.
> --Rick Butler
> "joseph.fanelli@.vba.va.gov" wrote:

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
Joe
Connections are always separate from each other, regardless of whether the
client connects using the same login information or not. Your temporary
tables will not collide or be visible to other connections.
Geoff N. Hiten
Microsoft SQL Server MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844860.949837.190280@.z14g2000cwz.googlegr oups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>
sql

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeConnections are always separate from each other, regardless of whether the
client connects using the same login information or not. Your temporary
tables will not collide or be visible to other connections.
Geoff N. Hiten
Microsoft SQL Server MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844860.949837.190280@.z14g2000cwz.googlegroups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeThe #tablename temp table exists only for the connection that calls the
proc. You are safe to call the same proc and the same table name as many
times as necessary without fear. Do not however use ##{tablename}. This will
cause a global temp table to be created and is referenceable by other
connections until the connection that created it is dropped.
--Rick Butler
"joseph.fanelli@.vba.va.gov" wrote:
> I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||Why not just select that one row and avoid creating a temp table? Improper
use of temporary objects in stored procedures can cause recompiles and
affect SQL performance. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;243586 for details.
Adrian
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BC656DA7-7BB2-4527-94D5-BCD53D702DE4@.microsoft.com...
> The #tablename temp table exists only for the connection that calls the
> proc. You are safe to call the same proc and the same table name as many
> times as necessary without fear. Do not however use ##{tablename}. This
> will
> cause a global temp table to be created and is referenceable by other
> connections until the connection that created it is dropped.
> --Rick Butler
> "joseph.fanelli@.vba.va.gov" wrote:
>> I have a stored procedure that creates a temporary table, populates it
>> with one record, and then returns that record to an ASP. I've read
>> that by prefixing the the tablename with a #, that only the connection
>> that created it can access it.
>> An IIS server will be making the connection and uses the same
>> connection string for all users. Even though the connection string is
>> the same, and the temporary table name is the same, will the temporary
>> table created only be available to the specific connection that created
>> it? I'm concerned that if multiple users make a request to that stored
>> procedure, they may get someone else's data.
>> thanks,
>> Joe
>>

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeThe #tablename temp table exists only for the connection that calls the
proc. You are safe to call the same proc and the same table name as many
times as necessary without fear. Do not however use ##{tablename}. This
will
cause a global temp table to be created and is referenceable by other
connections until the connection that created it is dropped.
--Rick Butler
"joseph.fanelli@.vba.va.gov" wrote:

> I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||Why not just select that one row and avoid creating a temp table? Improper
use of temporary objects in stored procedures can cause recompiles and
affect SQL performance. See
http://support.microsoft.com/defaul...kb;en-us;243586 for details.
Adrian
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BC656DA7-7BB2-4527-94D5-BCD53D702DE4@.microsoft.com...[vbcol=seagreen]
> The #tablename temp table exists only for the connection that calls the
> proc. You are safe to call the same proc and the same table name as many
> times as necessary without fear. Do not however use ##{tablename}. Th
is
> will
> cause a global temp table to be created and is referenceable by other
> connections until the connection that created it is dropped.
> --Rick Butler
> "joseph.fanelli@.vba.va.gov" wrote:
>

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeConnections are always separate from each other, regardless of whether the
client connects using the same login information or not. Your temporary
tables will not collide or be visible to other connections.
Geoff N. Hiten
Microsoft SQL Server MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844860.949837.190280@.z14g2000cwz.googlegroups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>

Question on SP_MakeWebTask

I'm using SP_MakeWebTask it's working OK. The problem is that if a
particular query returns no records, the string that gets written from
the:
<%begindetail%>
<%insert_data_here%>
<<%enddetail%>
part of the template file is: "The specified statement did not generate
any data"
Is there a way to substitute this string with a different one?
Thanks,
JoeHi
Have you tried using a UNION that returns an default record if your original
query returned nothing?
John
"joef" wrote:

> I'm using SP_MakeWebTask it's working OK. The problem is that if a
> particular query returns no records, the string that gets written from
> the:
> <%begindetail%>
> <%insert_data_here%>
> <<%enddetail%>
> part of the template file is: "The specified statement did not generate
> any data"
> Is there a way to substitute this string with a different one?
> Thanks,
> Joe
>sql

Question on reading from SQL SERVER

ok I use a datareader..Perhaps not the best method..but...if a better one exsits please let me know...
The problem...
I get info on a user...It returns an unknown amount of rows back...lets say two for instance...

ok...
How do I tell the number of rows...also the ability to reference each row and items in that row...
..
i.e. Row contains this

uname | password | address | Phone | notes

Like I said could contain multiple rows
I need to figure out info for specific rows and items in it...
in datareader I know you can reference each item which I guess you could use x number of variables to get each item in the row but that is tedious...
IS there a better answer??Use a DataAdapter/DataTable then.|||Perhaps you have a code example of how this might be attained|||Yes I do. And so does the rest of the sites google links to when you search those two.

DataTable oDataTable = new DataTable;
SqlDataAdapter oDataAdapter = new SqlDataAdapter(YourCommandObject);
oDataAdapter.Fill(oDataTable);
oDataAdapter.Dispose();
// close your objects
for (int i=0;i<oDataTable.Rows.Count;i++) {
 for (int j=0;j<oDataTable.Columns.Count;j++) {
 Console.WriteLine(oDataTable.Columns[j].ColumnName + " = " + oDataTable.Rows[i][j].ToString());
 }
}
oDataTable.Dispose();

question on query

Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
NeilYou didn't provide DDL. Here's an example from Northwind:
select
*
from
Customers c
left
join
Orders o on o.CustomerID = c.CustomerID
and o.OrderDate >= '19970101'
This shows all customers and any orders placed on or after 1 JAN 1997.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil|||Hi,
Go for LEFT OUTER JOIN. refer books online
Thanks
Hari
SQL Server MVP
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
> Hi,
> I need a query which returns all rows from one table and rows from another
> table if they are present.
> I also need to restrict the right hand table to certain rows.
> When I do this on;y the matching ros from RHS show.
> How d I get all rows from LHS and only atching rows in RHS?
> Many thanks,
> Neil
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Please read what you wrote. Does that sound like a spec that anyone
can use for programming? A vague question gets a vague answer -- look
up OUTER JOIN syntax in BOL.|||Sorry.
if I use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I get a list of all MediaIDs from TICList and can see if there are any
orders in tblDeliveryLine.
But I need to restrict my view of tblDeliveryLine to a certain order. When I
use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I no longer get the full list from the TICList.
NEIL|||On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:

>Sorry.
>if I use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I get a list of all MediaIDs from TICList and can see if there are any
>orders in tblDeliveryLine.
>But I need to restrict my view of tblDeliveryLine to a certain order. When
I
>use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I no longer get the full list from the TICList.
Hi Neil,
First, get rid of the TOP 100 PERCENT. This is just useless extra text
to make the query harder to read. Returning all rows is the default, so
you don't need to specify that you want all the rows. (And frankly, I'm
not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
clause, so you might even end up with a sub-optimal execution plan!).
The reason that your modification doesn't work, is that you filter on
the outer table in the where clause. This effectively changes the outer
join to an inner join. All rows from the first table without matching
rows from the second table get a set of NULLS as a result of the outer
join; since this NULL is never equal to 67, those rows are removed by
the WHERE filter. The solution is to move the filtering condition to the
JOIN condition.
Also, please start using table aliases instead of endlessly repeating
the same table name. Make them short and mnemonic for best effect.
Here's a version of your query that's not only corrected, but much
easier to read and maintain as well:
SELECT list.TICname, list.MediaID,
line.MediaID, line.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList AS list
LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
ON list.MediaID = line.MediaID
AND line.TICDeliveryHeadID = 67
ORDER BY list.TICname
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
I'm obliged for your help.
In fact I had already solved the problem with:
SELECT TICList.*, CurOrder.iOrder
FROM ngjarma_general.aycs_v_TICList TICList LEFT OUTER JOIN
(SELECT MediaID, iOrder
FROM ngjarma_general.aycs_tblTICDeliveryLine
WHERE (TICDeliveryHeadID = 67)) CurOrder ON TICList.MediaID =
CurOrder.MediaID
However, yours in much nicer and I'm changing my solution right now.
Thanks for the tips on shortening the code - I admit that I posted these
directly from EM, but I do usually tidy them up before using them in
Dreamweaver.
Once again, many thanks for the code and also the general tips.
NEIL
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:sj0q81puksr46e9l3qa2lqrqrjv0orkpi3@.
4ax.com...
> On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:
>
> Hi Neil,
> First, get rid of the TOP 100 PERCENT. This is just useless extra text
> to make the query harder to read. Returning all rows is the default, so
> you don't need to specify that you want all the rows. (And frankly, I'm
> not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
> clause, so you might even end up with a sub-optimal execution plan!).
> The reason that your modification doesn't work, is that you filter on
> the outer table in the where clause. This effectively changes the outer
> join to an inner join. All rows from the first table without matching
> rows from the second table get a set of NULLS as a result of the outer
> join; since this NULL is never equal to 67, those rows are removed by
> the WHERE filter. The solution is to move the filtering condition to the
> JOIN condition.
> Also, please start using table aliases instead of endlessly repeating
> the same table name. Make them short and mnemonic for best effect.
> Here's a version of your query that's not only corrected, but much
> easier to read and maintain as well:
> SELECT list.TICname, list.MediaID,
> line.MediaID, line.TICDeliveryHeadID
> FROM ngjarma_general.aycs_v_TICList AS list
> LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
> ON list.MediaID = line.MediaID
> AND line.TICDeliveryHeadID = 67
> ORDER BY list.TICname
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

question on query

Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil
You didn't provide DDL. Here's an example from Northwind:
select
*
from
Customers c
left
join
Orders o on o.CustomerID = c.CustomerID
and o.OrderDate >= '19970101'
This shows all customers and any orders placed on or after 1 JAN 1997.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil
|||Hi,
Go for LEFT OUTER JOIN. refer books online
Thanks
Hari
SQL Server MVP
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
> Hi,
> I need a query which returns all rows from one table and rows from another
> table if they are present.
> I also need to restrict the right hand table to certain rows.
> When I do this on;y the matching ros from RHS show.
> How d I get all rows from LHS and only atching rows in RHS?
> Many thanks,
> Neil
>
|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Please read what you wrote. Does that sound like a spec that anyone
can use for programming? A vague question gets a vague answer -- look
up OUTER JOIN syntax in BOL.
|||Sorry.
if I use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I get a list of all MediaIDs from TICList and can see if there are any
orders in tblDeliveryLine.
But I need to restrict my view of tblDeliveryLine to a certain order. When I
use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliver yHeadID
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDelive ryHeadID = 67)
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I no longer get the full list from the TICList.
NEIL
|||On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:

>Sorry.
>if I use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I get a list of all MediaIDs from TICList and can see if there are any
>orders in tblDeliveryLine.
>But I need to restrict my view of tblDeliveryLine to a certain order. When I
>use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliver yHeadID
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDelive ryHeadID = 67)
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I no longer get the full list from the TICList.
Hi Neil,
First, get rid of the TOP 100 PERCENT. This is just useless extra text
to make the query harder to read. Returning all rows is the default, so
you don't need to specify that you want all the rows. (And frankly, I'm
not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
clause, so you might even end up with a sub-optimal execution plan!).
The reason that your modification doesn't work, is that you filter on
the outer table in the where clause. This effectively changes the outer
join to an inner join. All rows from the first table without matching
rows from the second table get a set of NULLS as a result of the outer
join; since this NULL is never equal to 67, those rows are removed by
the WHERE filter. The solution is to move the filtering condition to the
JOIN condition.
Also, please start using table aliases instead of endlessly repeating
the same table name. Make them short and mnemonic for best effect.
Here's a version of your query that's not only corrected, but much
easier to read and maintain as well:
SELECT list.TICname, list.MediaID,
line.MediaID, line.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList AS list
LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
ON list.MediaID = line.MediaID
AND line.TICDeliveryHeadID = 67
ORDER BY list.TICname
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
I'm obliged for your help.
In fact I had already solved the problem with:
SELECT TICList.*, CurOrder.iOrder
FROM ngjarma_general.aycs_v_TICList TICList LEFT OUTER JOIN
(SELECT MediaID, iOrder
FROM ngjarma_general.aycs_tblTICDeliveryLine
WHERE (TICDeliveryHeadID = 67)) CurOrder ON TICList.MediaID =
CurOrder.MediaID
However, yours in much nicer and I'm changing my solution right now.
Thanks for the tips on shortening the code - I admit that I posted these
directly from EM, but I do usually tidy them up before using them in
Dreamweaver.
Once again, many thanks for the code and also the general tips.
NEIL
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:sj0q81puksr46e9l3qa2lqrqrjv0orkpi3@.4ax.com...
> On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:
>
> Hi Neil,
> First, get rid of the TOP 100 PERCENT. This is just useless extra text
> to make the query harder to read. Returning all rows is the default, so
> you don't need to specify that you want all the rows. (And frankly, I'm
> not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
> clause, so you might even end up with a sub-optimal execution plan!).
> The reason that your modification doesn't work, is that you filter on
> the outer table in the where clause. This effectively changes the outer
> join to an inner join. All rows from the first table without matching
> rows from the second table get a set of NULLS as a result of the outer
> join; since this NULL is never equal to 67, those rows are removed by
> the WHERE filter. The solution is to move the filtering condition to the
> JOIN condition.
> Also, please start using table aliases instead of endlessly repeating
> the same table name. Make them short and mnemonic for best effect.
> Here's a version of your query that's not only corrected, but much
> easier to read and maintain as well:
> SELECT list.TICname, list.MediaID,
> line.MediaID, line.TICDeliveryHeadID
> FROM ngjarma_general.aycs_v_TICList AS list
> LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
> ON list.MediaID = line.MediaID
> AND line.TICDeliveryHeadID = 67
> ORDER BY list.TICname
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

question on query

Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
NeilYou didn't provide DDL. Here's an example from Northwind:
select
*
from
Customers c
left
join
Orders o on o.CustomerID = c.CustomerID
and o.OrderDate >= '19970101'
This shows all customers and any orders placed on or after 1 JAN 1997.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil|||Hi,
Go for LEFT OUTER JOIN. refer books online
Thanks
Hari
SQL Server MVP
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
> Hi,
> I need a query which returns all rows from one table and rows from another
> table if they are present.
> I also need to restrict the right hand table to certain rows.
> When I do this on;y the matching ros from RHS show.
> How d I get all rows from LHS and only atching rows in RHS?
> Many thanks,
> Neil
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Please read what you wrote. Does that sound like a spec that anyone
can use for programming? A vague question gets a vague answer -- look
up OUTER JOIN syntax in BOL.|||Sorry.
if I use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =ngjarma_general.aycs_tblTICDeliveryLine.MediaID
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I get a list of all MediaIDs from TICList and can see if there are any
orders in tblDeliveryLine.
But I need to restrict my view of tblDeliveryLine to a certain order. When I
use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =ngjarma_general.aycs_tblTICDeliveryLine.MediaID
WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I no longer get the full list from the TICList.
NEIL|||On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:
>Sorry.
>if I use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I get a list of all MediaIDs from TICList and can see if there are any
>orders in tblDeliveryLine.
>But I need to restrict my view of tblDeliveryLine to a certain order. When I
>use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I no longer get the full list from the TICList.
Hi Neil,
First, get rid of the TOP 100 PERCENT. This is just useless extra text
to make the query harder to read. Returning all rows is the default, so
you don't need to specify that you want all the rows. (And frankly, I'm
not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
clause, so you might even end up with a sub-optimal execution plan!).
The reason that your modification doesn't work, is that you filter on
the outer table in the where clause. This effectively changes the outer
join to an inner join. All rows from the first table without matching
rows from the second table get a set of NULLS as a result of the outer
join; since this NULL is never equal to 67, those rows are removed by
the WHERE filter. The solution is to move the filtering condition to the
JOIN condition.
Also, please start using table aliases instead of endlessly repeating
the same table name. Make them short and mnemonic for best effect.
Here's a version of your query that's not only corrected, but much
easier to read and maintain as well:
SELECT list.TICname, list.MediaID,
line.MediaID, line.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList AS list
LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
ON list.MediaID = line.MediaID
AND line.TICDeliveryHeadID = 67
ORDER BY list.TICname
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
I'm obliged for your help.
In fact I had already solved the problem with:
SELECT TICList.*, CurOrder.iOrder
FROM ngjarma_general.aycs_v_TICList TICList LEFT OUTER JOIN
(SELECT MediaID, iOrder
FROM ngjarma_general.aycs_tblTICDeliveryLine
WHERE (TICDeliveryHeadID = 67)) CurOrder ON TICList.MediaID =CurOrder.MediaID
However, yours in much nicer and I'm changing my solution right now.
Thanks for the tips on shortening the code - I admit that I posted these
directly from EM, but I do usually tidy them up before using them in
Dreamweaver.
Once again, many thanks for the code and also the general tips.
NEIL
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:sj0q81puksr46e9l3qa2lqrqrjv0orkpi3@.4ax.com...
> On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:
>>Sorry.
>>if I use
>>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>>ngjarma_general.aycs_v_TICList.MediaID,
>> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>>Expr1
>>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
>> ngjarma_general.aycs_tblTICDeliveryLine ON
>>ngjarma_general.aycs_v_TICList.MediaID =>>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>>I get a list of all MediaIDs from TICList and can see if there are any
>>orders in tblDeliveryLine.
>>But I need to restrict my view of tblDeliveryLine to a certain order. When
>>I
>>use
>>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>>ngjarma_general.aycs_v_TICList.MediaID,
>> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>>Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
>>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
>> ngjarma_general.aycs_tblTICDeliveryLine ON
>>ngjarma_general.aycs_v_TICList.MediaID =>>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>>WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
>>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>>I no longer get the full list from the TICList.
> Hi Neil,
> First, get rid of the TOP 100 PERCENT. This is just useless extra text
> to make the query harder to read. Returning all rows is the default, so
> you don't need to specify that you want all the rows. (And frankly, I'm
> not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
> clause, so you might even end up with a sub-optimal execution plan!).
> The reason that your modification doesn't work, is that you filter on
> the outer table in the where clause. This effectively changes the outer
> join to an inner join. All rows from the first table without matching
> rows from the second table get a set of NULLS as a result of the outer
> join; since this NULL is never equal to 67, those rows are removed by
> the WHERE filter. The solution is to move the filtering condition to the
> JOIN condition.
> Also, please start using table aliases instead of endlessly repeating
> the same table name. Make them short and mnemonic for best effect.
> Here's a version of your query that's not only corrected, but much
> easier to read and maintain as well:
> SELECT list.TICname, list.MediaID,
> line.MediaID, line.TICDeliveryHeadID
> FROM ngjarma_general.aycs_v_TICList AS list
> LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
> ON list.MediaID = line.MediaID
> AND line.TICDeliveryHeadID = 67
> ORDER BY list.TICname
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

question on query

Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
NeilYou didn't provide DDL. Here's an example from Northwind:
select
*
from
Customers c
left
join
Orders o on o.CustomerID = c.CustomerID
and o.OrderDate >= '19970101'
This shows all customers and any orders placed on or after 1 JAN 1997.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil|||Hi,
Go for LEFT OUTER JOIN. refer books online
Thanks
Hari
SQL Server MVP
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
> Hi,
> I need a query which returns all rows from one table and rows from another
> table if they are present.
> I also need to restrict the right hand table to certain rows.
> When I do this on;y the matching ros from RHS show.
> How d I get all rows from LHS and only atching rows in RHS?
> Many thanks,
> Neil
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Please read what you wrote. Does that sound like a spec that anyone
can use for programming? A vague question gets a vague answer -- look
up OUTER JOIN syntax in BOL.|||Sorry.
if I use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I get a list of all MediaIDs from TICList and can see if there are any
orders in tblDeliveryLine.
But I need to restrict my view of tblDeliveryLine to a certain order. When I
use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I no longer get the full list from the TICList.
NEIL|||On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:

>Sorry.
>if I use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I get a list of all MediaIDs from TICList and can see if there are any
>orders in tblDeliveryLine.
>But I need to restrict my view of tblDeliveryLine to a certain order. When
I
>use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I no longer get the full list from the TICList.
Hi Neil,
First, get rid of the TOP 100 PERCENT. This is just useless extra text
to make the query harder to read. Returning all rows is the default, so
you don't need to specify that you want all the rows. (And frankly, I'm
not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
clause, so you might even end up with a sub-optimal execution plan!).
The reason that your modification doesn't work, is that you filter on
the outer table in the where clause. This effectively changes the outer
join to an inner join. All rows from the first table without matching
rows from the second table get a set of NULLS as a result of the outer
join; since this NULL is never equal to 67, those rows are removed by
the WHERE filter. The solution is to move the filtering condition to the
JOIN condition.
Also, please start using table aliases instead of endlessly repeating
the same table name. Make them short and mnemonic for best effect.
Here's a version of your query that's not only corrected, but much
easier to read and maintain as well:
SELECT list.TICname, list.MediaID,
line.MediaID, line.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList AS list
LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
ON list.MediaID = line.MediaID
AND line.TICDeliveryHeadID = 67
ORDER BY list.TICname
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
I'm obliged for your help.
In fact I had already solved the problem with:
SELECT TICList.*, CurOrder.iOrder
FROM ngjarma_general.aycs_v_TICList TICList LEFT OUTER JOIN
(SELECT MediaID, iOrder
FROM ngjarma_general.aycs_tblTICDeliveryLine
WHERE (TICDeliveryHeadID = 67)) CurOrder ON TICList.MediaID =
CurOrder.MediaID
However, yours in much nicer and I'm changing my solution right now.
Thanks for the tips on shortening the code - I admit that I posted these
directly from EM, but I do usually tidy them up before using them in
Dreamweaver.
Once again, many thanks for the code and also the general tips.
NEIL
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:sj0q81puksr46e9l3qa2lqrqrjv0orkpi3@.
4ax.com...
> On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:
>
> Hi Neil,
> First, get rid of the TOP 100 PERCENT. This is just useless extra text
> to make the query harder to read. Returning all rows is the default, so
> you don't need to specify that you want all the rows. (And frankly, I'm
> not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
> clause, so you might even end up with a sub-optimal execution plan!).
> The reason that your modification doesn't work, is that you filter on
> the outer table in the where clause. This effectively changes the outer
> join to an inner join. All rows from the first table without matching
> rows from the second table get a set of NULLS as a result of the outer
> join; since this NULL is never equal to 67, those rows are removed by
> the WHERE filter. The solution is to move the filtering condition to the
> JOIN condition.
> Also, please start using table aliases instead of endlessly repeating
> the same table name. Make them short and mnemonic for best effect.
> Here's a version of your query that's not only corrected, but much
> easier to read and maintain as well:
> SELECT list.TICname, list.MediaID,
> line.MediaID, line.TICDeliveryHeadID
> FROM ngjarma_general.aycs_v_TICList AS list
> LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
> ON list.MediaID = line.MediaID
> AND line.TICDeliveryHeadID = 67
> ORDER BY list.TICname
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)