Help with SQL Query

Dear group,
I need to create a stored procedure to return some data. The trick is the
data needs to be AGGREGATED with a SUM of commissions against each line for
each broker...
I have a table (TRADE) with the following data:
trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
1, 1, 1, 150, 13/06/2006
2, 2, 1, 100, 13/06/2006
3, 1, 1, 75, 14/06/2006
4, 1, 2, 165, 14/06/2006
5, 3, 1, 33.50, 14/06/2006
I want to display (for a DataTable to be used in a Crystal Report) a grid
where the headers will be:
Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id =
2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
(where tradeType_id = 2), Sum Monthly Total.
So that the query, when run on (14/06/2006), will look like:
1, 75, 165, 240, 225, 165, 190
2, null, null, null, 100, null, 100
3, 33.50, null, 33.50, 33.50, null, 33.50
The concept here is that I have a table which contains trades that a broker
has made. Each trade has a commission_amount column and a datestamp. I need
to be able to produce a report which has daily totals for different trade
types, but where the data is AGGREGATED by broker_id. All the SQL I've been
coming up with has been a total mess.
Can anyone assist with the above problem?
Many thanks!
MikeLiddle,
I think the trickiest thing here is the grouping. Is the monthly
total the running total since the first of the month, or just the sum
or trade type 2 records for a broker on a given day?
You can pretty easily group by date, broker id, and trade type.
SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
sumcom
FROM TRADE
GROUP BY broker_id, tradetype_id, datestamp
but the most straightforward way to get it into the format you want is
to do two subqueries and join them back together. But, what that looks
like will depend on whether you're looking for a running total or not.
Ion
Liddle Feesh wrote:
> Dear group,
> I need to create a stored procedure to return some data. The trick is the
> data needs to be AGGREGATED with a SUM of commissions against each line fo
r
> each broker...
> I have a table (TRADE) with the following data:
> trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
> 1, 1, 1, 150, 13/06/2006
> 2, 2, 1, 100, 13/06/2006
> 3, 1, 1, 75, 14/06/2006
> 4, 1, 2, 165, 14/06/2006
> 5, 3, 1, 33.50, 14/06/2006
> I want to display (for a DataTable to be used in a Crystal Report) a grid
> where the headers will be:
> Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id
=
> 2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
> (where tradeType_id = 2), Sum Monthly Total.
> So that the query, when run on (14/06/2006), will look like:
> 1, 75, 165, 240, 225, 165, 190
> 2, null, null, null, 100, null, 100
> 3, 33.50, null, 33.50, 33.50, null, 33.50
> The concept here is that I have a table which contains trades that a broke
r
> has made. Each trade has a commission_amount column and a datestamp. I nee
d
> to be able to produce a report which has daily totals for different trade
> types, but where the data is AGGREGATED by broker_id. All the SQL I've bee
n
> coming up with has been a total mess.
> Can anyone assist with the above problem?
> Many thanks!
> Mike|||<ionFreeman@.gmail.com> wrote in message
news:1150320207.374658.305960@.f6g2000cwb.googlegroups.com...
> Liddle,
> I think the trickiest thing here is the grouping. Is the monthly
> total the running total since the first of the month, or just the sum
> or trade type 2 records for a broker on a given day?
There are only four aggregated calculated fields, those are the daily totals
for trade_Type 1 and 2 and the monthly totals for trade_Type 1 and 2. The
sum is just an addition of those two fields and can be calculated easily by
hand.

> You can pretty easily group by date, broker id, and trade type.
> SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
> sumcom
> FROM TRADE
> GROUP BY broker_id, tradetype_id, datestamp
> but the most straightforward way to get it into the format you want is
> to do two subqueries and join them back together. But, what that looks
> like will depend on whether you're looking for a running total or not.
Cheers, Jon - that's close, but not quite right. I'm looking for a distinct
group, so that if there are only 2 broker_id's, there will be only two rows
and the SUM() data will be aggregated on those rows.
Thanks!sql

Help with SQL errors in Profiler

I noticed recently errors logged, each 5 second in the event viewer. I ran
Profiler by adding Errors and warning, RPCcompleted, Stored proc...
I see the same errors logged in profiler as the oners logged in event viewer
but can not find the text or the SP generating the error. errors in Profiler
appear as follows :
Exception : Error: 208, Severity: 16, State: 1
ErrorLog : 2004-09-23 09:24:43.82 spid66 Error: 208, Severity: 16, State: 1
2004-09-23 09:24:43.82 spid66 Invalid object name '##CC'...
My web site continu to work and I don't see error messages on my pages.
I use DBCC INPUTBUFFER(SPID), I get the following result :
RPC Event0sp_executesql;1
How can I configure or what should I do in my profiler to discover the
statement or SP that causing the error. I should signal that just before
error messages I have RPC:Completed messages for different SP
Hi,
Please make sure that you have events from TSQL group included in your
trace. The most useful would be:
SQL:StmtStarting or SQL:BatchStarting.
Regards,
Maciek Sarnowicz
SQL Server Tools SDE
This posting is provided "AS IS" with no warranties, and confers no rights.
"SalamElias" <eliassal@.online.nospam> wrote in message
news:8FEBA1EB-DE4A-47B4-B154-A9B7532F17C3@.microsoft.com...
>I noticed recently errors logged, each 5 second in the event viewer. I ran
> Profiler by adding Errors and warning, RPCcompleted, Stored proc...
> I see the same errors logged in profiler as the oners logged in event
> viewer
> but can not find the text or the SP generating the error. errors in
> Profiler
> appear as follows :
> Exception : Error: 208, Severity: 16, State: 1
> ErrorLog : 2004-09-23 09:24:43.82 spid66 Error: 208, Severity: 16,
> State: 1
> 2004-09-23 09:24:43.82 spid66 Invalid object name '##CC'...
> My web site continu to work and I don't see error messages on my pages.
> I use DBCC INPUTBUFFER(SPID), I get the following result :
> RPC Event 0 sp_executesql;1
> How can I configure or what should I do in my profiler to discover the
> statement or SP that causing the error. I should signal that just before
> error messages I have RPC:Completed messages for different SP
|||Hi and so many thanks.
I set up profiler sessions where I capture SQL_BatchStarting & RPC:Starting
or SQL:stmtstarting
I get
---Start Profiler
result--
exec [PEP_ETC]..sp_procedure_params_rowset N'CreateSearchSP', 1, NULL, NULL
SET NO_BROWSETABLE ON
RPC:Starting
declare @.P1 int
set @.P1=-1
exec sp_prepare @.P1 output, N'@.P1 varchar(2),@.P2 varchar(8),@.P3
varchar(3),@.P4 varchar(1),@.P5 varchar(2700),@.P6 varchar(255),@.P7
varchar(100),@.P8 varchar(600),@.P9 varchar(3),@.P10 varchar(3),@.P11
varchar(2),@.P12 varchar(100)', N'exec CreateSearchSP @.P1, @.P2, @.P3, @.P4, @.P5,
@.P6, @.P7, @.P8, @.P9, @.P10, @.P11, @.P12', 1
select @.P1
SQL_BatchStarting
SET FMTONLY ON exec sp_execute 7,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','
',' ' SET FMTONLY OFF
---End Profiler
result--
then the error comes here
---Start Profiler
result--
EXCEPTION : Error: 208, Severity: 16, State: 1
ErrorLog
2004-09-28 15:18:38.45 spid66 Error: 208, Severity: 16, State: 1
2004-09-28 15:18:38.45 spid66 Invalid object name '##CC'..
---End Profiler
result--
In the SP CreateSearchSP, I have a dynamic insert statement as follows :
EXEC "Select xx, yyyy into ##CC" + @.sTableName + " where labalablab "
This SP procedure is called from several places (ASP pages and from within
other SPs inside SQL) with same user and IIS appli.
So what I need to know is just the text that is calling this stored procedure.
When I browse the page that heavily use it, I don't see any error. So it is
sure either when SQL under heavy load can't execute it correctly or the error
is generated when calling it from somewhere else which I would like to
discover.
So what I need to know is just the text that is calling this stored procedure?
"Maciek Sarnowicz [MSFT]" wrote:

> Hi,
> Please make sure that you have events from TSQL group included in your
> trace. The most useful would be:
> SQL:StmtStarting or SQL:BatchStarting.
> Regards,
> Maciek Sarnowicz
> SQL Server Tools SDE
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:8FEBA1EB-DE4A-47B4-B154-A9B7532F17C3@.microsoft.com...
>
>
sql

Help with SQL errors in Profiler

I noticed recently errors logged, each 5 second in the event viewer. I ran
Profiler by adding Errors and warning, RPCcompleted, Stored proc...
I see the same errors logged in profiler as the oners logged in event viewer
but can not find the text or the SP generating the error. errors in Profiler
appear as follows :
Exception : Error: 208, Severity: 16, State: 1
ErrorLog : 2004-09-23 09:24:43.82 spid66 Error: 208, Severity: 16, State: 1
2004-09-23 09:24:43.82 spid66 Invalid object name '##CC'...
My web site continu to work and I don't see error messages on my pages.
I use DBCC INPUTBUFFER(SPID), I get the following result :
RPC Event0sp_executesql;1
A microsofot consultanty asked me to set up profiler sessions where I
capture SQL_BatchStarting & RPC:Starting
or SQL:stmtstarting
I get
---Start Profiler
result--
exec [PEP_ETC]..sp_procedure_params_rowset N'CreateSearchSP', 1, NULL, NULL
SET NO_BROWSETABLE ON
RPC:Starting
declare @.P1 int
set @.P1=-1
exec sp_prepare @.P1 output, N'@.P1 varchar(2),@.P2 varchar(8),@.P3
varchar(3),@.P4 varchar(1),@.P5 varchar(2700),@.P6 varchar(255),@.P7
varchar(100),@.P8 varchar(600),@.P9 varchar(3),@.P10 varchar(3),@.P11
varchar(2),@.P12 varchar(100)', N'exec CreateSearchSP @.P1, @.P2, @.P3, @.P4, @.P5,
@.P6, @.P7, @.P8, @.P9, @.P10, @.P11, @.P12', 1
select @.P1
SQL_BatchStarting
SET FMTONLY ON exec sp_execute 7,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','
',' ' SET FMTONLY OFF
---End Profiler
result--
then the error comes here
---Start Profiler
result--
EXCEPTION : Error: 208, Severity: 16, State: 1
ErrorLog
2004-09-28 15:18:38.45 spid66 Error: 208, Severity: 16, State: 1
2004-09-28 15:18:38.45 spid66 Invalid object name '##CC'..
---End Profiler
result--
In the SP CreateSearchSP, I have a dynamic insert statement as follows :
EXEC "Select xx, yyyy into ##CC" + @.sTableName + " where labalablab "
This SP procedure is called from several places (ASP pages and from within
other SPs inside SQL) with same user and IIS appli.
So what I need to know is just the text that is calling this stored procedure.
When I browse the page that heavily use it, I don't see any error. So it is
sure either when SQL under heavy load can't execute it correctly or the error
is generated when calling it from somewhere else which I would like to
discover.
So what I need to know is just the text that is calling this stored procedure?
Try following steps.
Place a filter on SPIDS to see only that spid that is resulting into error.
That should give you the commands being executed from that spid only.
Hope fro there you should be able to figure out which SP is calling it.
|||Thanks for the response. How can Ifigure out the SPID before running profiler?
I am not so expert in SPID maniuplating, If I am not mistaken, between the
momemnt where you have a SPID and the moment t for exemple to use dbcc
inputbuffer, the sql phrase might change, no ?
Thanks
"Vikas Kumar" wrote:

> Try following steps.
> Place a filter on SPIDS to see only that spid that is resulting into error.
> That should give you the commands being executed from that spid only.
> Hope fro there you should be able to figure out which SP is calling it.
>

Help with Sql Cache Dependency

Hello all.. i'm having a major issue with my Sql Express 2005 database :(

I'm using wicked code sitemap to allows for a sitemap to be stored in a database.. it uses sql cache dependency to invalidated the cache bla bla

Problem:

After i update a record / add new record the database generates a SqlQueryNotificationStoredProcedure

But it never gets executed. I've done tests on the code it's calling everything accordingly so i'm assuming the problem lies in a configuration setting of the database

done alot of searching around the net and i've found this

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username

i'm using Windows Authentication how do i run this and will this solve the problem

Thank you in advance

Chris

Any ideas anyone?|||

Well I've found something out..

The sitemap provided from wicked code only invalidates the cache when a new item has been inserted.. from what i can tell anyway

So updates are not displayed.. to get around this problem i've disallowed updates to the sitemap.. i could of writen a stored procedure to delete and recreate the entry however.

If you try deleting and recreating be careful of the ordering if you have set the ID to auto increment.

Happy Coding :D

All the above isWRONG.. i thought it was updating because of visual studios virutal webserver type of thing.. read below post for the correct answer..

Chris

|||

For all those ppl out there strugling with the Wicked Code Sitemap I have some new findings....

Please post thanks if i've helped anyone so i know weather it's worth updating these answers lol..

first of all

1. SQL EXPRESS 2005 DOES NOT SUPPORT QUERY NOTIFICATIONS, so site map will not update on database changes!!!

2. took me hours to find that out stupid me should read up on sql express before introducing it.. :(

3. to fix the updating problem add the following procedure to the SqlSiteMapProvider

publicvoid Refresh()

{

Clear();

_nodes.Clear();

_root =null;

}

personally i just refresh the sitemap on every page request in my master page... this isn't the best way.. only did it because the site doesn't get much traffic and it was easy bad bad bad programmer chris.. lol.. you should only refresh the sitemap on database changes..

please help my mate get more hitswww.drakesterling.com that will display the sitemap too.. i had to do alot of modifications to the CSS adapters to get it working properly any questions on what's shown here i'm happy to answer..

Happy programming all

From Chris

sql

Help with SQL 2000 Debugger

I installed the SQL 2000 trial as a local server on a standalone XP Pro system. When use the debugger, it executes the stored procedure without allowing me to step into the code.

Any help would be greatly appreciated.

Check in the help topic in SQL Server Books Online titled: Troubleshooting the Transact-SQL Debugger

You can experience the problems you are having when the DCOM settings aren't configured correctly.

-Sue

|||

Sue, thanks for responding. Yes I've gone thru the DCOM settings and made sure EVERYONE was there and set to allow, SYSTEM is also there and set to allow. The identy for sqldbreg is set at interactive like described in the Troubleshooting the Transact-SQL Debugger section of the books online. You should know that I'm not a windows op sys expert. I think it has something to do with the Windows Services Account is the Local System Account. All the Services Account stuff is well beyond my knowledge or experience and I don't know if since my system is XP Pro wether I can even setup a Domain User Account?

Any more help would be appreciated.

|||Sue, I just figured it out. I had to change the MSSQLSERVER properties logon to logon using a User Account versus the Local System.|||

Can you please tell me where do I go to set MSSQLServer properties logon? Should I be a SysAdmin/DBA and have access to the server box.

Thank you.

|||

In SQL Server 2000, use Enterprise Manager to change the service accounts. Right click on the server and select properties. Then go to the Security tab.

In SQL Server 2005, you would use Configuration Manager to change the service accounts.

-Sue

Help with SQL 2000 Debugger

I installed the SQL 2000 trial as a local server on a standalone XP Pro system. When use the debugger, it executes the stored procedure without allowing me to step into the code.

Any help would be greatly appreciated.

Check in the help topic in SQL Server Books Online titled: Troubleshooting the Transact-SQL Debugger

You can experience the problems you are having when the DCOM settings aren't configured correctly.

-Sue

|||

Sue, thanks for responding. Yes I've gone thru the DCOM settings and made sure EVERYONE was there and set to allow, SYSTEM is also there and set to allow. The identy for sqldbreg is set at interactive like described in the Troubleshooting the Transact-SQL Debugger section of the books online. You should know that I'm not a windows op sys expert. I think it has something to do with the Windows Services Account is the Local System Account. All the Services Account stuff is well beyond my knowledge or experience and I don't know if since my system is XP Pro wether I can even setup a Domain User Account?

Any more help would be appreciated.

|||Sue, I just figured it out. I had to change the MSSQLSERVER properties logon to logon using a User Account versus the Local System.|||

Can you please tell me where do I go to set MSSQLServer properties logon? Should I be a SysAdmin/DBA and have access to the server box.

Thank you.

|||

In SQL Server 2000, use Enterprise Manager to change the service accounts. Right click on the server and select properties. Then go to the Security tab.

In SQL Server 2005, you would use Configuration Manager to change the service accounts.

-Sue

sql

Help with SP syntax

I have the stored proc. below and I'm passing two
parameters. What I'm trying to do is if either one of
the parameters is equal to "All", then change the value
of the paramter to an empty string or set another
variable to an empty string. SQL doesn't like the code I
have below. Please help.
CREATE PROCEDURE GetUSFSUsers
(
@.Role nvarchar(100),
@.Unit nvarchar(20)
)
AS
Declare @.Role2 nvarchar(100)
Declare @.Unit2 nvarchar(20)
If @.Role = 'All'
@.Role2 = ''
Else
@.Role2 = @.Role
If @.Unit = 'All'
@.Unit2 = ''
Else
@.Unit2 = @.UnitYou can change it to
if @.role = 'all'
set @.role2 = ''
else
set @.role2 = @.role
if @.unit = 'all'
set @.unit2 = ''
else
set @.unit2 = @.unit
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Help with SP or Function

I was trying to write this MDX but I am getting errors so maybe I should
create a stored procedure against my CUBE.
I want to return two values : Revenue Avg and Product Price Avg by a
specific time and Sales Region.
can someone provide an example?
My cube provides the total revenue for any date. I thought I could take the
avg(Sales Region.members,Time.members, total revenue amount) AS 'Revenue Avg
'
avg(Sales Region.members, Time.members, Product price) AS 'Product Price Avg
'
and get a value but I am getting an error message.What error message? Where?
ML
http://milambda.blogspot.com/

Help with SP

I have a webform that I need to insert data into a db. I would like to have a stored procedure handle the insert for me.

Here are the data fields from the form:

<td><asp:textbox id="f_name" runat="server"></asp:textbox></td>
<td><asp:textbox id="l_name" runat="server"></asp:textbox></td>
<td><asp:textbox id="emp_num" runat="server"></asp:textbox></td>
<td><asp:textbox id="email" runat="server"></asp:textbox></td>
<td><asp:dropdownlist id=dd1 runat="server" DataMember="Line_Item" DataSource="<%# dsLineItem1 %>" DataTextField="LI_ID" DataValueField="ID"></asp:dropdownlist></td>
<td><asp:dropdownlist id=dd2 runat="server" DataMember="Component" DataSource="<%# dsComponent1 %>" DataTextField="Component" DataValueField="ID"></asp:dropdownlist></td>
<td><asp:dropdownlist id=dd3 runat="server" DataMember="Activity" DataSource="<%# dsActivity1 %>" DataTextField="Abbrev" DataValueField="ID"></asp:dropdownlist></td>


Needed Solution:
I need to create a stored procedure that will insert the data from the above fields into the following database:

ProfileDB
Table columns:
id
f_name
l_name
emp_num
email
line_item
component
activity

I am sure that there are some rules for this I am unaware of (such as field names must match table column names etc.).

Thank you for your help.

BTW,

My version of SQL is 2000.

Thank you again.

Sincerely,

TimI have constucted a stored procedure that is at the least error free:
If you have any comments or suggestions regarding this sp I would greatly appreciate your input since I am new to SQL.

CREATE PROCEDURE dbo.InsertProfile
(
@.F_Name [varchar] (100),
@.L_Name [varchar] (100),
@.Emp_Num [numeric] (9),
@.Email [varchar] (250),
@.Line_Item [varchar] (250),
@.Component [varchar] (250),
@.Activity [varchar] (250)
)

AS

Insert into [dbo.InsertProfile]
(
[F_Name],
[L_Name],
[Emp_Num],
[Email],
[Line_Item],
[Component],
[Activity]
)

Values

(@.F_Name, @.L_Name, @.Emp_Num, @.Email, @.Line_Item, @.Component, @.Activity)

GO

Thank you.|||OK

You might want to check for errors

DECLARE @.Error, @.Rowcount

...sql statement

SELECT @.Error = @.@.ERROR, @.RowCount = @.@.Rowcount

Then interogate thos values...if @.Error is other than 0, then you have a problem...

And if @.Rowcount doesn't = 1 the that's a problem as well (don't know how that would ever happen, but it's a check)

Help with slow query please...

I've inherited a piece of SQL that takes an age to run (it's dynamically
created, not in a stored procedure)
---
SELECT
DISTINCT tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN tableB
ON tableB.fieldA =
tableA.fieldA
INNER JOIN tableC
ON tableC.fieldC =
tableB.fieldC
WHERE
tableC.fieldD IN ('para1', 'para2')
AND
tableC.fieldE = 'para3'
AND
ISNULL(tableC.fieldF, 0) = 0
ORDER BY
tableA.fieldB
---
I've checked the database when this runs and it shows object locks on tableB
and tableC (at the page and table level).
I changed the SQL so that each table name in the FROM clause has the 'WITH
(NOLOCK)' hint on, but this led to no obvious improvement, and the object
locks remained the same.
So, I looked at the execution plan. Nothing horrific...one nested loop
(55%) and one table spool/lazy spool (32%).
So, I ran an index analysis.
This suggested that I create the following index:
CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
[fieldA] ASC )
However, when I check tableB, it already has the following indexes:
1 - clustered index on fieldC
2 - non-clustered index on fieldA and fieldC
Okay, the for the existing non-clustered index, the left and right columns
have been swapped, and I know that the left column is the all important on,
but I'm not sure what to do here...
I could either:
a - re-jig the non-clustered index to swap the columns around, but I've no
idea whether this will improve things and presumably it may break it for
other queries?
b - remove the index altogether and replace it with a single column index
for fieldA (as there's already a single columned clustered index on fieldC).
Suggestions please....
Thanks
GriffTry,
SELECT DISTINCT
tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN
tableB
ON tableB.fieldA = tableA.fieldA
INNER JOIN
tableC
ON tableC.fieldC = tableB.fieldC
and tableC.fieldD IN ('para1', 'para2')
AND tableC.fieldE = 'para3'
AND (tableC.fieldF = 0 or tableC.fieldF is null)
ORDER BY
tableA.fieldB;
Post tables definition including primary keys, DRI constraints and indexes
for a better analysis.
AMB
"Griff" wrote:

> I've inherited a piece of SQL that takes an age to run (it's dynamically
> created, not in a stored procedure)
> ---
> SELECT
> DISTINCT tableA.fieldA,
> tableA.fieldB
> FROM
> tableA
> INNER JOIN tableB
> ON tableB.fieldA =
> tableA.fieldA
> INNER JOIN tableC
> ON tableC.fieldC =
> tableB.fieldC
> WHERE
> tableC.fieldD IN ('para1', 'para2')
> AND
> tableC.fieldE = 'para3'
> AND
> ISNULL(tableC.fieldF, 0) = 0
> ORDER BY
> tableA.fieldB
> ---
> I've checked the database when this runs and it shows object locks on tabl
eB
> and tableC (at the page and table level).
> I changed the SQL so that each table name in the FROM clause has the 'WITH
> (NOLOCK)' hint on, but this led to no obvious improvement, and the object
> locks remained the same.
> So, I looked at the execution plan. Nothing horrific...one nested loop
> (55%) and one table spool/lazy spool (32%).
> So, I ran an index analysis.
> This suggested that I create the following index:
> CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
> [fieldA] ASC )
> However, when I check tableB, it already has the following indexes:
> 1 - clustered index on fieldC
> 2 - non-clustered index on fieldA and fieldC
> Okay, the for the existing non-clustered index, the left and right columns
> have been swapped, and I know that the left column is the all important on
,
> but I'm not sure what to do here...
> I could either:
> a - re-jig the non-clustered index to swap the columns around, but I've no
> idea whether this will improve things and presumably it may break it for
> other queries?
> b - remove the index altogether and replace it with a single column index
> for fieldA (as there's already a single columned clustered index on fieldC
).
> Suggestions please....
> Thanks
> Griff
>
>|||Griff wrote:
> ISNULL(tableC.fieldF, 0) = 0
This is a problem right here. Never apply a function against a table column
in the WHERE clause: it forces a table scan. Unfortunately in this case
(where you want either 0 or Null), it's going to be tough to create a
sargable criterion. You can try
(tableC.fieldF = 0 OR tableC.fieldF IS NULL)
but I suspect this will be non-sargable as well (due to the OR condition)
You can also try a subquery containing a union:
SELECT
DISTINCT tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN tableB
ON tableB.fieldA = tableA.fieldA
INNER JOIN (
SELECT fieldC,fieldD,fieldE
FROM TableC
WHERE fieldF IS NULL
UNION ALL
SELECT fieldC,fieldD,fieldE
FROM TableC
WHERE fieldF = 0) C
ON C.fieldC =tableB.fieldC
WHERE
tableC.fieldD IN ('para1', 'para2')
AND
tableC.fieldE = 'para3'
ORDER BY
tableA.fieldB
Of course, the DISTINCT keyword isn't helping performance. Is there any way
to eliminate that?
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Tried your suggestion, but it actually took a bit longer...(3 minutes cf 2.5
minutes)
However, now got it down to sub 1 second by re-ordering the inner joins!
I think that this is due to the fact that first time I was getting a small
amount of data in tableA and inner joining it with a huge amount of data in
tableB (and a fairly huge amount of data in tableC).
I changed the order so that it now gets the big table first and inner joins
it with the smaller table. I'm not sure WHY it's faster as it's got to be
doing the same net amount of work, but the query plan is significantly
different.
Now, the only thing left to do is to change the ASP code that generates the
dynamic ASP code.
Thanks everyone...
Griff|||Have you consider running DBCC DBREINDEX and/or DBCC SHOWCONTIG against the
largest tables + DBCC INDEXDEFRAG?
"Griff" wrote:

> I've inherited a piece of SQL that takes an age to run (it's dynamically
> created, not in a stored procedure)
> ---
> SELECT
> DISTINCT tableA.fieldA,
> tableA.fieldB
> FROM
> tableA
> INNER JOIN tableB
> ON tableB.fieldA =
> tableA.fieldA
> INNER JOIN tableC
> ON tableC.fieldC =
> tableB.fieldC
> WHERE
> tableC.fieldD IN ('para1', 'para2')
> AND
> tableC.fieldE = 'para3'
> AND
> ISNULL(tableC.fieldF, 0) = 0
> ORDER BY
> tableA.fieldB
> ---
> I've checked the database when this runs and it shows object locks on tabl
eB
> and tableC (at the page and table level).
> I changed the SQL so that each table name in the FROM clause has the 'WITH
> (NOLOCK)' hint on, but this led to no obvious improvement, and the object
> locks remained the same.
> So, I looked at the execution plan. Nothing horrific...one nested loop
> (55%) and one table spool/lazy spool (32%).
> So, I ran an index analysis.
> This suggested that I create the following index:
> CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
> [fieldA] ASC )
> However, when I check tableB, it already has the following indexes:
> 1 - clustered index on fieldC
> 2 - non-clustered index on fieldA and fieldC
> Okay, the for the existing non-clustered index, the left and right columns
> have been swapped, and I know that the left column is the all important on
,
> but I'm not sure what to do here...
> I could either:
> a - re-jig the non-clustered index to swap the columns around, but I've no
> idea whether this will improve things and presumably it may break it for
> other queries?
> b - remove the index altogether and replace it with a single column index
> for fieldA (as there's already a single columned clustered index on fieldC
).
> Suggestions please....
> Thanks
> Griff
>
>|||Hi Bob
Thanks for the response. As you can see from one of my responses in this
thread, I've actually solved the problem.
Latest incarnation of the SQL simply changed the table from which the first
field is retrieved from:
> SELECT
> DISTINCT tableA.fieldA
To:

> SELECT
> DISTINCT tableB.fieldA
And the query gone from ~3 mins to sub 1 second. Amazing!
Not sure how I would get rid of the DISTINCT clause...it does exactly what
I need it to do.
Anyhow, I've learnt two things from your post [1 - not using a function; 2 -
the word "sargable" (which I had to look up)] so a day definitely not
wasted.
Thx
Griff|||Griff wrote:
> Hi Bob
> Thanks for the response. As you can see from one of my responses in
> this thread, I've actually solved the problem.
> Latest incarnation of the SQL simply changed the table from which the
> first field is retrieved from:
> To:
>
> And the query gone from ~3 mins to sub 1 second. Amazing!
> Not sure how I would get rid of the DISTINCT clause...it does
> exactly what I need it to do.
>
There may be another way to accomplish the same task. Without details about
your database structure, it is impossible to make any suggestions.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Griff,
You did not post DDL (another abbreviation to look up :-)), so we cannot
tell what the keys of the different tables are, and what the real
indexes are.
The fact that changing tableA.fieldA to tableB.fieldA increases the
performance with orders of magnitude indicates that you might not have
properly defined the keys (or unique indexes).
Also, in this case you should pay particular attention to the indexes of
tableC, especially if it contains many rows. Since you dit not post DDL,
I cannot give a real suggestion here.
Gert-Jan
Griff wrote:
> Hi Bob
> Thanks for the response. As you can see from one of my responses in this
> thread, I've actually solved the problem.
> Latest incarnation of the SQL simply changed the table from which the firs
t
> field is retrieved from:
> To:
>
> And the query gone from ~3 mins to sub 1 second. Amazing!
> Not sure how I would get rid of the DISTINCT clause...it does exactly wha
t
> I need it to do.
> Anyhow, I've learnt two things from your post [1 - not using a function; 2
-
> the word "sargable" (which I had to look up)] so a day definitely not
> wasted.
> Thx
> Griffsql

Help with simple SP (newbie)

Hi. I'm writing my firsts stored procedures.
I'm trying to get a row from one table and insert the results in other
table. By now I have this:
create proc registerTable
@.email varchar(30)
as
select name,email
from users
where email=@.email
go
insert into employees
exec registerTable 'users'
go
But I need to include the insert in the procedure. How can I do that?
Regards,
Diego F.
What do you mean by that ?
--But I need to include the insert in the procedure. How can I do that?
Do you want to execute this recursive ?
Jens.
"Diego F." wrote:

> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
>
|||No. I mean that I need all done into the SP. Now I select the row and
outside I make the insert. I want to call the SP and have all done.
BTW, I put that in the wrong group, sorry.
Regards,
Diego F.
"Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> escribi en el
mensaje news:19270E44-FE1A-4030-AABE-9BD4ECFF7838@.microsoft.com...[vbcol=seagreen]
> What do you mean by that ?
> --But I need to include the insert in the procedure. How can I do that?
> Do you want to execute this recursive ?
> Jens.
> "Diego F." wrote:
|||I dont quite get your question. Is this what you were expecting?
create proc registerTable
@.email varchar(30)
as
insert into employees
select name,email
from users
where email=@.email
go
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Diego F." <diegofrNO@.terra.es> wrote in message
news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
|||Yes, it was exactly that :-)
Regards,
Diego F.
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> escribi en el mensaje
news:db85pe$rc0$1@.news01.intel.com...
>I dont quite get your question. Is this what you were expecting?
> create proc registerTable
> @.email varchar(30)
> as
> insert into employees
> select name,email
> from users
> where email=@.email
> go
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
>

Help with simple SP (newbie)

Hi. I'm writing my firsts stored procedures.
I'm trying to get a row from one table and insert the results in other
table. By now I have this:
create proc registerTable
@.email varchar(30)
as
select name,email
from users
where email=@.email
go
insert into employees
exec registerTable 'users'
go
But I need to include the insert in the procedure. How can I do that?
--
Regards,
Diego F.What do you mean by that ?
--But I need to include the insert in the procedure. How can I do that?
Do you want to execute this recursive ?
Jens.
"Diego F." wrote:
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
>|||No. I mean that I need all done into the SP. Now I select the row and
outside I make the insert. I want to call the SP and have all done.
BTW, I put that in the wrong group, sorry.
--
Regards,
Diego F.
"Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> escribió en el
mensaje news:19270E44-FE1A-4030-AABE-9BD4ECFF7838@.microsoft.com...
> What do you mean by that ?
> --But I need to include the insert in the procedure. How can I do that?
> Do you want to execute this recursive ?
> Jens.
> "Diego F." wrote:
>> Hi. I'm writing my firsts stored procedures.
>> I'm trying to get a row from one table and insert the results in other
>> table. By now I have this:
>> create proc registerTable
>> @.email varchar(30)
>> as
>> select name,email
>> from users
>> where email=@.email
>> go
>> insert into employees
>> exec registerTable 'users'
>> go
>> But I need to include the insert in the procedure. How can I do that?
>> --
>> Regards,
>> Diego F.
>>
>>|||I dont quite get your question. Is this what you were expecting?
create proc registerTable
@.email varchar(30)
as
insert into employees
select name,email
from users
where email=@.email
go
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Diego F." <diegofrNO@.terra.es> wrote in message
news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>|||Yes, it was exactly that :-)
--
Regards,
Diego F.
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> escribió en el mensaje
news:db85pe$rc0$1@.news01.intel.com...
>I dont quite get your question. Is this what you were expecting?
> create proc registerTable
> @.email varchar(30)
> as
> insert into employees
> select name,email
> from users
> where email=@.email
> go
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
>> Hi. I'm writing my firsts stored procedures.
>> I'm trying to get a row from one table and insert the results in other
>> table. By now I have this:
>> create proc registerTable
>> @.email varchar(30)
>> as
>> select name,email
>> from users
>> where email=@.email
>> go
>> insert into employees
>> exec registerTable 'users'
>> go
>> But I need to include the insert in the procedure. How can I do that?
>> --
>> Regards,
>> Diego F.
>>
>>
>

Help with simple SP (newbie)

Hi. I'm writing my firsts stored procedures.
I'm trying to get a row from one table and insert the results in other
table. By now I have this:
create proc registerTable
@.email varchar(30)
as
select name,email
from users
where email=@.email
go
insert into employees
exec registerTable 'users'
go
But I need to include the insert in the procedure. How can I do that?
Regards,
Diego F.What do you mean by that ?
--But I need to include the insert in the procedure. How can I do that?
Do you want to execute this recursive ?
Jens.
"Diego F." wrote:

> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
>|||No. I mean that I need all done into the SP. Now I select the row and
outside I make the insert. I want to call the SP and have all done.
BTW, I put that in the wrong group, sorry.
Regards,
Diego F.
"Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> escribi en el
mensaje news:19270E44-FE1A-4030-AABE-9BD4ECFF7838@.microsoft.com...[vbcol=seagreen]
> What do you mean by that ?
> --But I need to include the insert in the procedure. How can I do that?
> Do you want to execute this recursive ?
> Jens.
> "Diego F." wrote:
>|||I dont quite get your question. Is this what you were expecting?
create proc registerTable
@.email varchar(30)
as
insert into employees
select name,email
from users
where email=@.email
go
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Diego F." <diegofrNO@.terra.es> wrote in message
news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>|||Yes, it was exactly that :-)
Regards,
Diego F.
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> escribi en el mensaje
news:db85pe$rc0$1@.news01.intel.com...
>I dont quite get your question. Is this what you were expecting?
> create proc registerTable
> @.email varchar(30)
> as
> insert into employees
> select name,email
> from users
> where email=@.email
> go
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
>sql

Help with simple insert, how to use primary key?

Ive added a primary key called ID to my table, now my insert stored procedure dont no longer work.

i want an unique identifier for each row.

heres my stored procedure:

CREATE PROCEDURE composeMessage

-- Add the parameters for the stored procedure here

@.username varchar(24),

@.sender varchar(24),

@.date dateTime,

@.subject varchar(255),

@.message varchar(2500)

AS

BEGIN

insert into Messages(

"Username",

"Sender",

"Date",

"Subject",

"Message"

)

values (

@.username,

@.sender,

@.date,

@.subject,

@.message

)

END

GO

heres my sqlcreate table:

USE [Messenger]

GO

/****** Object: Table [dbo].[Messages] Script Date: 09/12/2006 15:13:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Messages](

[Username] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,

[Sender] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,

[Subject] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,

[Message] [varchar](2500) COLLATE Latin1_General_CI_AS NOT NULL,

[Date] [datetime] NOT NULL,

[ID] [int] NOT NULL,

CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

As primary keycan't be null, what do i put for primary key for my insert to work?

hope you understand what i mean?

Am i right that i have to set the table designer/identity column to my primary key?

It generates an unique incresing number, so doi i use that?

|||

If you want to have an increasing value, you will have to switch on the identity property on your column.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Help with SELECT Query

I'm not a SQL expert. I want to be able to write a stored procedure
that will return 'people who bought this product also bought this...'.

I have a user table that links to a transaction table that links to a
transaction items table that links to the products table:

(User Table)
UserID
Other user data

(Transaction Table)
TransactionID
UserID
Other transaction data such as the date and the transaction result

(TransactionItem Table)
TransactionItemID
TransactionID
ProductID

(Product Table)
ProductID
Other product data

If I try to nest a SELECT query to give me the list of UserIDs for all
users who purchased a given ProductID then SQL Server gets very upset
as Nested Querys are only supposed to return a single value.

So, how do I do this? Build the first list of UserIDs and then select
all other ProductIDs for the users in the list excluding the original
ProductID?

I'm certain this must be a fairly straight forward thing for a SQL
server guru so any help would be appreciated...

Thanks

JimHi

See how to help use by posting DDL
http://www.aspfaq.com/etiquett*e.asp?id=5006 and example data.

This will get them for products X abd Y if they bought them in the same
transaction and you want details from both products:

SELECT U.[UserID], U.[Other user data], T.[TransactionID],
I.[TransactionItemID],
P.[ProductID],P.[Other product data], J.[TransactionItemID],
Q.[ProductID],Q.[Other product data]
FROM [User Table] U
JOIN [Transaction Table] T ON U.[UserID] = T.[UserID]
JOIN [TransactionItem Table] I ON T.[TransactionID] = I.[TransactionID]
JOIN [Product Table] P ON I.[ProductID] = P.[ProductID] AND P.[Other product
data] = 'X'
JOIN [TransactionItem Table] J ON T.[TransactionID] = J.[TransactionID]
JOIN [Product Table] Q ON J.[ProductID] = Q.[ProductID] AND Q.[Other product
data] = 'Y'

Alternatively:

SELECT U.[UserID], U.[Other user data], T.[TransactionID],
I.[TransactionItemID],
P.[ProductID],P.[Other product data], J.[TransactionItemID],
Q.[ProductID],Q.[Other product data]
FROM [User Table] U
JOIN [Transaction Table] T ON U.[UserID] = T.[UserID]
JOIN [TransactionItem Table] I ON T.[TransactionID] = I.[TransactionID]
JOIN [Product Table] P ON I.[ProductID] = P.[ProductID] AND P.[Other product
data] = 'X'
WHERE EXISTS ( SELECT * FROM
JOIN [TransactionItem Table] J
JOIN [Product Table] Q ON J.[ProductID] = Q.[ProductID] AND Q.[Other product
data] = 'Y'
WHERE T.[TransactionID] = J.[TransactionID])

Or if you want it at user level and not necessarily in the same transaction:

SELECT U.[UserID], U.[Other user data], T.[TransactionID],
I.[TransactionItemID],
P.[ProductID],P.[Other product data], J.[TransactionItemID],
Q.[ProductID],Q.[Other product data]
FROM [User Table] U
JOIN [Transaction Table] T ON U.[UserID] = T.[UserID]
JOIN [TransactionItem Table] I ON T.[TransactionID] = I.[TransactionID]
JOIN [Product Table] P ON I.[ProductID] = P.[ProductID] AND P.[Other product
data] = 'X'
WHERE EXISTS ( SELECT * FROM
JOIN [Transaction Table] S
JOIN [TransactionItem Table] J ON S.[TransactionID] = J.[TransactionID]
JOIN [Product Table] Q ON J.[ProductID] = Q.[ProductID] AND Q.[Other product
data] = 'Y'
WHERE U.[UserID] = S.[UserID] )

John

<jimh@.netwasp.com> wrote in message
news:1115485245.378812.90490@.f14g2000cwb.googlegro ups.com...
> I'm not a SQL expert. I want to be able to write a stored procedure
> that will return 'people who bought this product also bought this...'.
> I have a user table that links to a transaction table that links to a
> transaction items table that links to the products table:
> (User Table)
> UserID
> Other user data
> (Transaction Table)
> TransactionID
> UserID
> Other transaction data such as the date and the transaction result
> (TransactionItem Table)
> TransactionItemID
> TransactionID
> ProductID
> (Product Table)
> ProductID
> Other product data
> If I try to nest a SELECT query to give me the list of UserIDs for all
> users who purchased a given ProductID then SQL Server gets very upset
> as Nested Querys are only supposed to return a single value.
> So, how do I do this? Build the first list of UserIDs and then select
> all other ProductIDs for the users in the list excluding the original
> ProductID?
> I'm certain this must be a fairly straight forward thing for a SQL
> server guru so any help would be appreciated...
> Thanks
> Jim|||This is called a relational division. Here is my usual "cut & paste"
about it. You might want to get a copy of SQL FOR SMARTIES for your
desk:

Relational division is one of the eight basic operations in Codd's
relational algebra. The idea is that a divisor table is used to
partition a dividend table and produce a quotient or results table.
The quotient table is made up of those values of one column for which a
second column had all of the values in the divisor.

This is easier to explain with an example. We have a table of pilots
and the planes they can fly (dividend); we have a table of planes in
the hangar (divisor); we want the names of the pilots who can fly every
plane (quotient) in the hangar. To get this result, we divide the
PilotSkills table by the planes in the hangar.

CREATE TABLE PilotSkills
(pilot CHAR(15) NOT NULL,
plane CHAR(15) NOT NULL,
PRIMARY KEY (pilot, plane));

PilotSkills
pilot plane
=========================
'Celko' 'Piper Cub'
'Higgins' 'B-52 Bomber'
'Higgins' 'F-14 Fighter'
'Higgins' 'Piper Cub'
'Jones' 'B-52 Bomber'
'Jones' 'F-14 Fighter'
'Smith' 'B-1 Bomber'
'Smith' 'B-52 Bomber'
'Smith' 'F-14 Fighter'
'Wilson' 'B-1 Bomber'
'Wilson' 'B-52 Bomber'
'Wilson' 'F-14 Fighter'
'Wilson' 'F-17 Fighter'

CREATE TABLE Hangar
(plane CHAR(15) NOT NULL PRIMARY KEY);

Hangar
plane
=============
'B-1 Bomber'
'B-52 Bomber'
'F-14 Fighter'

PilotSkills DIVIDED BY Hangar
pilot
=============================
'Smith'
'Wilson'

In this example, Smith and Wilson are the two pilots who can fly
everything in the hangar. Notice that Higgins and Celko know how to
fly a Piper Cub, but we don't have one right now. In Codd's original
definition of relational division, having more rows than are called for
is not a problem.

The important characteristic of a relational division is that the CROSS
JOIN (Cartesian product) of the divisor and the quotient produces a
valid subset of rows from the dividend. This is where the name comes
from, since the CROSS JOIN acts like a multiplication operator.

Division with a Remainder

There are two kinds of relational division. Division with a remainder
allows the dividend table to have more values than the divisor, which
was Codd's original definition. For example, if a pilot can fly more
planes than just those we have in the hangar, this is fine with us.
The query can be written in SQL-89 as

SELECT DISTINCT pilot
FROM PilotSkills AS PS1
WHERE NOT EXISTS
(SELECT *
FROM Hangar
WHERE NOT EXISTS
(SELECT *
FROM PilotSkills AS PS2
WHERE (PS1.pilot = PS2.pilot)
AND (PS2.plane = Hangar.plane)));

The quickest way to explain what is happening in this query is to
imagine an old World War II movie where a cocky pilot has just walked
into the hangar, looked over the fleet, and announced, "There ain't no
plane in this hangar that I can't fly!" We are finding the pilots for
whom there does not exist a plane in the hangar for which they have no
skills. The use of the NOT EXISTS() predicates is for speed. Most SQL
systems will look up a value in an index rather than scan the whole
table. The SELECT * clause lets the query optimizer choose the column
to use when looking for the index.

This query for relational division was made popular by Chris Date in
his textbooks, but it is not the only method nor always the fastest.
Another version of the division can be written so as to avoid three
levels of nesting. While it is not original with me, I have made it
popular in my books.

SELECT PS1.pilot
FROM PilotSkills AS PS1, Hangar AS H1
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar);

There is a serious difference in the two methods. Burn down the
hangar, so that the divisor is empty. Because of the NOT EXISTS()
predicates in Date's query, all pilots are returned from a division by
an empty set. Because of the COUNT() functions in my query, no pilots
are returned from a division by an empty set.

In the sixth edition of his book, INTRODUCTION TO DATABASE SYSTEMS
(Addison-Wesley; 1995 ;ISBN 0-201-82458-2), Chris Date defined another
operator (DIVIDEBY ... PER) which produces the same results as my
query, but with more complexity.

Exact Division

The second kind of relational division is exact relational division.
The dividend table must match exactly to the values of the divisor
without any extra values.

SELECT PS1.pilot
FROM PilotSkills AS PS1
LEFT OUTER JOIN
Hangar AS H1
ON PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar);

This says that a pilot must have the same number of certificates as
there planes in the hangar and these certificates all match to a plane
in the hangar, not something else. The "something else" is shown by a
created NULL from the LEFT OUTER JOIN.

Please do not make the mistake of trying to reduce the HAVING clause
with a little algebra to:

HAVING COUNT(PS1.plane) = COUNT(H1.plane)

because it does not work; it will tell you that the hangar has (n)
planes in it and the pilot is certified for (n) planes, but not that
those two sets of planes are equal to each other.

Note on Performance

The nested EXISTS() predicates version of relational division was made
popular by Chris Date's textbooks, while the author is associated with
popularizing the COUNT(*) version of relational division. The Winter
1996 edition of DB2 ON-LINE MAGAZINE
(http://www.db2mag.com/96011ar:htm) had an article entitled "Powerful
SQL:Beyond the Basics" by Sheryl Larsen which gave the results of
testing both methods. Her conclusion for DB2 was that the nested
EXISTS() version is better when the quotient has less than 25% of the
dividend table's rows and the COUNT(*) version is better when the
quotient is more than 25% of the dividend table.|||John

Thanks very much for your response. I think I may not have been quite
clear enough in my question. What I want to query the database for is:
'for every person who bought product 'X' give me a list of all the
other products they have bought?' This is very similar to the Amazon
site when you are about to purchase a book - underneath it says "people
who bought this book also bought these ones..."

Jim|||>> 'for every person who bought product 'X' give me a list of all the
other products they have bought? <<

SELECT DISTINCT T1.user_id, T1.product_id
FROM Transactions AS T1
WHERE EXISTS
(SELECT *
FROM Transactions AS T2
WHERE T1.user_id = T2.user_id
AND product_id = 'X');

But I would do it this way to get more meaningful data in the
aggregate:

SELECT T1.product_id, COUNT(*) AS pairing_tally, COUNT(DISTINCT
user_id) AS buyer_tally
FROM Transactions AS T1
WHERE EXISTS
(SELECT *
FROM Transactions AS T2
WHERE T1.user_id = T2.user_id
AND product_id = 'X')
AND T1.product_id <> 'X'
GROUP BY T1.product_id
HAVING COUNT(*) > @.my_threshold;sql

Help with rewriting code without cursor

Hello,
Just wondering if anyone can tell me the best way to rewrite the below code
without a cursor.
It's just passing each Id to a stored procedure.
Let me know if you need any more info.
Thanks & go easy on me, I know cursors tend to rile everyone up.
Declare cur_DeleteStuff Cursor Scroll For
Select distinct TableID
from tbl_DTM
Where APID IN
(Select TableID from tbl_DTM where
supplierID = @.v_FromSupplierID)
Open cur_DeleteStuff
Fetch First FROM cur_DeleteStiff into @.ChildTableID
While (@.@.Fetch_Status <> -1)
Begin
exec sp_SMART_ANADeleteFrom @.ChildTableID, 1, 0
If @.@.Error <> 0
BEGIN
ROLLBACK Transaction CDTTransfer
RAISERROR('Something Bad Happened, Updates ROLLED BACK!',1,1)
RETURN
END
Fetch Next FROM cur_Deletestuff into @.ChildTableID
END
Close cur_DeleteStuff
Deallocate cur_DeleteStuff"Lesley" <Lesley@.discussions.microsoft.com> wrote in message
news:F2C468A7-7573-4FE4-8FDC-D8D75D2AE374@.microsoft.com...
> Hello,
> Just wondering if anyone can tell me the best way to rewrite the below
> code
> without a cursor.
> It's just passing each Id to a stored procedure.
> Let me know if you need any more info.
> Thanks & go easy on me, I know cursors tend to rile everyone up.
>
What is the code for the stored procedure: sp_SMART_ANADeleteFrom
If the sp_SMART_ANADeleteFrom procedure is performing some type of delete
based on the ChildTableID
then you should be able to modify the delete to do something like the
following:
DELETE TableName
WHERE ChildTableID IN
(Select distinct TableID
from tbl_DTM
Where APID IN
(Select TableID from tbl_DTM where
supplierID = @.v_FromSupplierID))
One a side note: You should probably not be naming your user defined stored
procedures with an sp_ prefix. The sp_ prefix while not disallowed, is
generally use for SQL Server system stored procedure which are found in the
master database and are available globally throughout the system.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for your help Rick,
Though the naming convention implies it's only deleting a child - it's
actually doing something completely different.
I still need to call the stored procedure for each table ID found.
Thanks for the sp_ info.
Lesley
"Rick Sawtell" wrote:

> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:F2C468A7-7573-4FE4-8FDC-D8D75D2AE374@.microsoft.com...
> What is the code for the stored procedure: sp_SMART_ANADeleteFrom
> If the sp_SMART_ANADeleteFrom procedure is performing some type of delete
> based on the ChildTableID
> then you should be able to modify the delete to do something like the
> following:
> DELETE TableName
> WHERE ChildTableID IN
> (Select distinct TableID
> from tbl_DTM
> Where APID IN
> (Select TableID from tbl_DTM where
> supplierID = @.v_FromSupplierID))
>
> One a side note: You should probably not be naming your user defined stor
ed
> procedures with an sp_ prefix. The sp_ prefix while not disallowed, is
> generally use for SQL Server system stored procedure which are found in th
e
> master database and are available globally throughout the system.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Whatever it does, we can't help you find a set-baset solution without you
posting the procedure.
ML|||Sorry, I was thinking I could just use the sp as is. I pasted it below. It
was written a while ago by someone else & is in production now.
Basically it's deleting rows from a table, then deleting the defining row
from another table based on the tableID
I'd welcome any input on how to change this to set based.
That may also address rollback issues I predict I will have.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_SMART_ANADeleteFrom
@.FromTableID INT = 0,
@.OKtoDeleteORIG BIT = 0,
@.DebugMode INT = 0
AS
DECLARE @.TableType CHAR(3)
DECLARE @.AnalyticParentID INT
DECLARE @.FromPhysicalTableName varchar(255)
DECLARE @.strSQL nvarchar(2000)
if @.FromTableID is null
begin
raiserror ('Invalid From Table ID.',1,1)
return
end
--
SELECT @.TableType = MyType,
@.AnalyticParentID = AnalyticParentID,
@.FromPhysicalTableName = PhysicalDataTableName
from
tbl_DataTableMaster where Tableid = @.FromTableID
BEGIN TRANSACTION DELETEfromAnalytics
-- Delete the rows from the Quarterly ANA table
SET @.StrSQL = N'DELETE FROM My_Users.' + @.FromPhysicalTableName +
N' WHERE TableID = ' + rtrim(convert(char(10),@.FromTableID))
if @.DebugMode <> 0
begin
print '-- DELETE Statement --'
print @.strsql
end
EXEC (@.StrSQL)
if @.@.Error <> 0
begin
ROLLBACK Transaction
Raiserror('Error deleting rows. Table Deletion did NOT occur!!',1,1)
RETURN
end
--delete row from tbl_DataTableMaster
SET @.strSQL = N'DELETE FROM tbl_DataTableMaster ' +
N' WHERE TableID = ' + rtrim(convert(char(10),@.FromTableID))
if @.DebugMode <> 0
begin
print '-- DELETE data table master Statement --'
print @.strsql
end
EXEC (@.StrSQL)
if @.@.Error <> 0
begin
ROLLBACK Transaction
Raiserror('Error Deleting in Data Table Master. Table Deletion did NOT
occur!!',1,1)
RETURN
end
--
COMMIT TRANSACTION DELETEfromAnalytics
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql

Help with reuse methology?

I find that many stored procedures are necessary in nearly all databases I
use. Creating dynamic (one that takes a string and executes the sql in it)
stored procedures is expensive performance wise. However, they do allow you
to pass in strings of sql and execute it. I find in general that SPs are
created and can only be used for one specific thing - registration form
input, account info update, etc. For example, I can't take the account
update SP and use it for the registration input SP.
Is there some general or fundamental guide that should be followed to lower
the overall number of stored procedures in a database?
Thanks,
BrettBrett wrote:
> I find that many stored procedures are necessary in nearly all
> databases I use. Creating dynamic (one that takes a string and
> executes the sql in it) stored procedures is expensive performance
> wise. However, they do allow you to pass in strings of sql and
> execute it. I find in general that SPs are created and can only be
> used for one specific thing - registration form input, account info
> update, etc. For example, I can't take the account update SP and use
> it for the registration input SP.
> Is there some general or fundamental guide that should be followed to
> lower the overall number of stored procedures in a database?
> Thanks,
> Brett
Not really. You need what you need. Dynamic SQL is not the way to go.
It's really no different than executing the SQL statement directly from
the client and exposes your database to SQL injection and performance
issues.
Writing stored procedures is just a part of the application development
process. You can leverage things like functions or utility-type stored
procs to simplify repetitive code, but not much you can do to lower the
number of procedures you need.
David Gugick
Imceda Software
www.imceda.com

Help with reformatting xml

This is a simplistic example of what I’m trying to achieve;
I have some XML stored in a table (in a column of type xml):
<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>
And I want to generate new xml as follows:
<report>
<somevalue>13</somevalue>
</report>
Where 13 is the total value of all somevalue attributes.
I’ve been looking at the query() and nodes() methods and cannot quite do
what I want to do although I feel it might be possible. Currently I’m simply
selecting the xml and letting my client application do the formatting,
however I feel that this might cause some performance issues on large blobs
of xml data.
Is there a better way to do this?
Many thanks in advance for any help with this.
Julia Beresford.
Something like this?
DECLARE @.doc XML;
SELECT @.doc = N'<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>';
SELECT @.doc.query('<report>
<somevalue>
{ fn:sum(/root/node/@.somevalue) }
</somevalue>
</report>');
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:5A5E83A9-F098-454D-80FA-FA212A3B15E8@.microsoft.com...
> This is a simplistic example of what I'm trying to achieve;
> I have some XML stored in a table (in a column of type xml):
> <root>
> <node id="1" somevalue="0" />
> <node id="2" somevalue="1" />
> <node id="3" somevalue="5" />
> <node id="4" somevalue="7" />
> </root>
> And I want to generate new xml as follows:
> <report>
> <somevalue>13</somevalue>
> </report>
> Where 13 is the total value of all somevalue attributes.
> I've been looking at the query() and nodes() methods and cannot quite do
> what I want to do although I feel it might be possible. Currently I'm
> simply
> selecting the xml and letting my client application do the formatting,
> however I feel that this might cause some performance issues on large
> blobs
> of xml data.
> Is there a better way to do this?
> Many thanks in advance for any help with this.
> Julia Beresford.
>

Help with reformatting xml

This is a simplistic example of what I’m trying to achieve;
I have some XML stored in a table (in a column of type xml):
<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>
And I want to generate new xml as follows:
<report>
<somevalue>13</somevalue>
</report>
Where 13 is the total value of all somevalue attributes.
I’ve been looking at the query() and nodes() methods and cannot quite do
what I want to do although I feel it might be possible. Currently I’m sim
ply
selecting the xml and letting my client application do the formatting,
however I feel that this might cause some performance issues on large blobs
of xml data.
Is there a better way to do this?
Many thanks in advance for any help with this.
Julia Beresford.Something like this?
DECLARE @.doc XML;
SELECT @.doc = N'<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>';
SELECT @.doc.query('<report>
<somevalue>
{ fn:sum(/root/node/@.somevalue) }
</somevalue>
</report>');
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:5A5E83A9-F098-454D-80FA-FA212A3B15E8@.microsoft.com...
> This is a simplistic example of what I'm trying to achieve;
> I have some XML stored in a table (in a column of type xml):
> <root>
> <node id="1" somevalue="0" />
> <node id="2" somevalue="1" />
> <node id="3" somevalue="5" />
> <node id="4" somevalue="7" />
> </root>
> And I want to generate new xml as follows:
> <report>
> <somevalue>13</somevalue>
> </report>
> Where 13 is the total value of all somevalue attributes.
> I've been looking at the query() and nodes() methods and cannot quite do
> what I want to do although I feel it might be possible. Currently I'm
> simply
> selecting the xml and letting my client application do the formatting,
> however I feel that this might cause some performance issues on large
> blobs
> of xml data.
> Is there a better way to do this?
> Many thanks in advance for any help with this.
> Julia Beresford.
>

Help with Recursive Stored Procedure

Hello,

I am having problem writing a recursive stored procedure for a Content Management System and am hoping that someone can give me a hand with it. I have posted (below) a script to recreate a table and data to test it with, and I've included my sample stored procedure that isn't working as expected. Any help would really be appreciated, I'm stuck.

I have a table which represents a hierarchical view of data. In this case, it is an example of classified ads. The hierarchy looks something like this:

............
Classifieds
--Animals
----Dogs
-----Golden Retrievers
-----Poodles
--Automobiles
............

Each of these above I call a NODE. Each NODE has a Parent Node, and since a NODE can have multiple Parent Nodes, each NODE also has a PriorParentID (which is its parent's parent node). (still with me here?) Each NODE supports many different Types of Content. What I need to do is walk the hierarchy from a specific NODE, up through each of it's parents to the top-most node. Throughout the recursion up the tree, I need to collect a distinct list of Content Types that the parents support.

So, suppose that the "Classifieds" node supports "ContentTypeID" of 3. Every ascendent node below it inherits that "ContentTypeID". So if I am looking at "Poodles", I want to walk the tree up through its parents, collecting the "ContentTypeID" all the way up, I'd end up with a "3" for a ContentTypeID for Poodles because one of it's parents in the hierarchy supports ContentTypeID "3".

And if the "Dogs" node supports a "ContentTypeID" of 1 and "Animals" supports a "ContentTypeID" of 1 and 2, then I need to get back 1, 2 and 3 in a resultset...because as you walk up the hierarchy from Poodles to Classifieds, you encounter a "1" and a "2" and a "3" for ContentTypeID's.

Here's a script to set up the data:

---------------

CREATE TABLE [tmpNodalHierarchy] (
[ChildNodeID] [int] NULL ,
[ParentNodeID] [int] NULL ,
[PriorParentNodeID] [int] NULL ,
[NodeLabel] nvarchar(50) NULL,
[ContentTypeID] [int] NULL)
GO

INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(1, 0, -1, 'Classifieds', 3)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(2, 1, 0, 'Animals', 1)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(2, 1, 0, 'Animals', 2)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(3, 1, 0, 'Automobiles', 1)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(4, 2, 1, 'Dogs', 1)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(5, 4, 2, 'Golden Retrievers', 1)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(5, 4, 2, 'Golden Retrievers', 2)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(6, 4, 2, 'Poodles', 1)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(7, 4, 2, 'Chows', NULL)
---------------

CREATE PROC dbo.tmpGetRecursiveContentTypes
(
@.ParentNodeID int,
@.PriorParentNodeID int,
@.ContentTypeID int = 0,
@.InContentTypes nvarchar(500) = '',
@.OutContentTypes nvarchar(500) = null OUTPUT
)
AS
IF NOT @.PriorParentNodeID IS NULL
BEGIN
SET NOCOUNT ON
DECLARE @.ContentTypes nvarchar(500)
SELECT @.ContentTypes = @.InContentTypes
DECLARE @.curContentTypeID int, @.tmpPriorParentNodeID int, @.curNodeLabel nvarchar(100), @.curChildNodeID int, @.curParentNodeID int, @.curPriorParentNodeID int

WHILE 0=0
BEGIN
SELECT TOP 1 @.curContentTypeID = ContentTypeID, @.curParentNodeID = ParentNodeID, @.curPriorParentNodeID = PriorParentNodeID FROM dbo.tmpNodalHierarchy WHERE ChildNodeID=@.ParentNodeID and ParentNodeID = @.PriorParentNodeID and (ContentTypeID > @.ContentTypeID) ORDER BY ContentTypeID ASC
IF @.@.ROWCOUNT = 0
BEGIN
SELECT @.curContentTypeID = ContentTypeID, @.curParentNodeID = ParentNodeID, @.curPriorParentNodeID = PriorParentNodeID FROM dbo.tmpNodalHierarchy WHERE ChildNodeID=@.ParentNodeID and ParentNodeID = @.PriorParentNodeID and (ContentTypeID = @.ContentTypeID) ORDER BY ContentTypeID ASC
SET @.ContentTypeID = @.curContentTypeID
BREAK
END
SET @.ContentTypeID = @.curContentTypeID

IF NOT @.curContentTypeID IS NULL
SELECT @.ContentTypes = @.ContentTypes + ',' + CAST(@.curContentTypeID as nvarchar(10))
END
EXEC dbo.tmpGetRecursiveContentTypes @.curParentNodeID,@.curPriorParentNodeID,@.ContentTypeID,@.ContentTypes
END
SET @.OutContentTypes = @.ContentTypes
IF @.@.NESTLEVEL=1
SELECT @.ContentTypes
GO

----------------
--To execute the sproc as if we wanted
--to get the ContentTypeID's for "Poodles"
--
--
EXEC tmpGetRecursiveContentTypes 4, 2
--
----------------

I have been concatenating the results, but ideally I want the resultset to look like:

ContentTypeIDs
------
1
2
3

Thanks for any help you can give.

DanHave you ever thought to use XML to present these data?|||Search booksonline for 'Expanding hierarchies', pretty good example|||I thought of using XML, but I can't use it...it needs to return a recordset because it needs to easily adapt to other databases other than SQL Server.

Dan|||Thanks for the link Dutch. It inspired an idea that worked and resulted in an even simpler solution. Thanks a lot.

Dan|||Big, I'm not going to write your stored proc for you, but I can point you in a direction. The basic problem you have is getting all parents of a leaf node. if you have all the parents, a simple select will give you all the ContentTypeID's right? Ok, so let's forget about the ContentTypeIDs and focus on the real problem - getting that list of parents given a certain node.

First: get rid of the PriorParentID - and whack yourself on the head with a blunt object 10 times repeating 'I can get the prior parent Id be looking at the parent's ParentNodeID'

Then: Please normalise your tables! One node = Many ContentTypeIDs, so:

Table1:
ChildNodeID, ParentNodeID, NodeLabel

Table2:
ChildNodeID, ContentTypeID

So you'll have this in the tables:

Table1:
1, null, Classifieds
2, 1, Animals
3, 1, Automobiles
4, 2, Dogs
5, 4, Golden...
6, 4, Poodles
7, 4, Cows

Table2:
1, 3
2, 1
2, 2
3, 1
4, 1
5, 1
5, 2
6, 1

Now all you do is this:

1 Start at leaf node
2 Add the ContentTypeIDs for the node to your list - don't add ones in the list already
3 Get the parentID
4 if the parentID is null, finish
5 Else get the node where id = parentID
6 go to 2

Recursive, which is bad for performance. So: how do we do this without recursion? Textbook answer is by using a bridge table. What is a bridge table? It's a table that lists all parent-child relationships. Example - this is a condensed version of your table1: (ChildId, ParentId)

1, null
2, 1
3, 1
4, 2
5, 4
6, 4
7, 4

This is the brige table for it: (ChildID, ParentID)
2, 1
3, 1
4, 1
5, 1
6, 1
7, 1
4, 2
5, 2
6, 2
7, 2
5, 4
6, 4
7, 4

As you can see, with one select from the bridge table I can get all parents of a node. E.g. in your example, you looked at poodles, node 6, and you wanted all parents:
select ParentID
from Bridge
where ChildId = 6

and you get all the parent nodes: 1, 2, 4 (classifieds, animals and dogs) and now with one simple join, and perhaps a distinct, you can get all the ContentTypeIds

select distinct Table2.ContentTypeID
from Bridge
join Table2
on Bridge.ChildID = 6
and Bridge.ParentID = Table2.ID

How do you build / maintain the bridge table? Triggers on Table1.

Phew... Enough for now.