Conditional Table Joins

Hi all,
I'm having a little trouble with producing a query for MS SQL Server
2000 with a conditional join in.
The basis of the structure is that when an order is active, the
OrderID of any book in the Order is stored in the book record in the
Books table. If an order is cancelled, the OrderID is removed from the
record in the Books table, to enable the book to be put back on sale.
At that point, for each book cancelled, a record is appended to the
CancelledRefundedBooks table, with just the book reference number
(foreign key of the Books table), and the OrderID it belonged to. This
is to us to still see which books were in a cancelled order. I hope
that made sense! i've posted the table defs for these tables at the
bottom of this message.
My problem is when using the following stored procedure query to
search for orders based on different criteria, included a reference
number for a book in the order, the join with the books table is
causing cancelled orders not to be returned. For example, if i search
for active order 1, because one of the possible search criteria is
book reference, the Books table is joined to the query. Because order
1 is active, the book records in the Books table have orderID's
stored, and the order is returned. However, if i search for cancelled
order 2, because the books are lacking the OrderID field, the join is
causing the order record not to be returned. I have tried using an
INNER JOIN and a LEFT OUTER JOIN, and neither help. The ideal
situation would be if the join for the Books table could be
conditional on the OrderStatusID field in the Orders table, so that
the query would be joined to the Books table if the order was active,
and if the order was cancelled, the join would change to the
CancelledRefundedBooks table.
An additional complication is that if the join was to the
CancelledRefundedBooks table, in the event of a cancelled order, the
CancelledRefundedBooks table holds only the book reference numbers.
This means that table would then need a further join onto the Books
table to retrieve author, title etc. fields.
The stored proc as it stands is as follows:
ALTER PROCEDURE sp_searchorders
@.refnumber int = NULL,
@.surname nvarchar(100) = NULL,
@.orderid int = NULL,
@.postcode nvarchar(50) = NULL,
@.booktitle nvarchar(500) = NULL
WITH RECOMPILE
AS
SELECT DISTINCT
Orders.OrderID, Staff.Surname AS StaffSur,
Staff.FirstName AS StaffFir, Orders.CustomerID, Customers.Forenames,
Customers.Surname,
Customers.Telephone, Customers.Email,
Customers.Forenames, Customers.Surname, Customers.TownCity,
Websites.Name AS Website, Orders.OrdDate
FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
Staff ON Orders.StaffID = Staff.StaffID INNER
JOIN
Websites ON Orders.WebsiteID = Websites.WebsiteID LEFT OUTER JOIN
Books ON Orders.OrderID = Books.OrderID
WHERE CASE @.refnumber
WHEN 0 THEN @.refnumber
ELSE Books.Ref
END
= @.refnumber
AND Customers.Surname LIKE COALESCE(@.surname, '%')
AND Books.Title LIKE COALESCE(@.booktitle, '%')
AND Customers.Postcode LIKE COALESCE(@.postcode, '%')
AND CASE @.orderid
WHEN 0 THEN @.orderid
ELSE Orders.OrderID
END
= @.orderid
My sincerest apologies for posting such a long narrative about the
problem, but I can't think of any other way of describing it! I have
included the CREATE statements for all the mentioned tables below, and
if i can provide any more information to help come up with a solution
plz ask. I am a relative newbie at SQL server, so please be gentle!!
Many thanks in advance
James Currer
CREATE TABLE [Books] (
[Ref] [int] NOT NULL ,
[Author] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Title] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[PlacePubDate] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[Keywords] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Catalogues] [nvarchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[Cost] [money] NULL ,
[DealerCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Notes] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[Price] [money] NULL ,
[OrderID] [int] NULL ,
[Weight] [numeric](6, 3) NULL ,
[ISBN] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Row] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Shelf] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[LocationID] [int] NULL ,
[StatusID] [int] NULL ,
[DealerID] [int] NULL ,
[BoxID] [int] NULL ,
[LastUpdatedBy] [int] NULL ,
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[Ref]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [CancelledRefundedBooks] (
[BookRef] [int] NOT NULL ,
[RefundID] [int] NULL ,
[OrderID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[StaffID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[OrdDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[WebsiteID] [int] NOT NULL ,
[PackingID] [int] NULL ,
[PaymentID] [int] NULL ,
[InvoiceNumber] [int] NULL ,
[InvoiceDate] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OrderStatusID] [int] NOT NULL CONSTRAINT [DF_Orders_OrderStatusID]
DEFAULT (4),
[TotalPricePaid] [money] NOT NULL CONSTRAINT
[DF_Orders_TotalPricePaid] DEFAULT (0),
[CustomersOwnRef] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ShippingPaid] [money] NOT NULL CONSTRAINT [DF_Orders_ShippingPaid]
DEFAULT (0.00),
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GOJames,
I did not go beyond your second paragraph. I believe your db design is
flawed. If you keep an OrderID in your Book table for an active order, how
do you deal with the situation that there more than one orders that buy the
same book?
"James Currer" <phaser2001@.hotmail.com> wrote in message
news:57df26d.0307300626.667496bf@.posting.google.com...
> Hi all,
> I'm having a little trouble with producing a query for MS SQL Server
> 2000 with a conditional join in.
> The basis of the structure is that when an order is active, the
> OrderID of any book in the Order is stored in the book record in the
> Books table. If an order is cancelled, the OrderID is removed from the
> record in the Books table, to enable the book to be put back on sale.
> At that point, for each book cancelled, a record is appended to the
> CancelledRefundedBooks table, with just the book reference number
> (foreign key of the Books table), and the OrderID it belonged to. This
> is to us to still see which books were in a cancelled order. I hope
> that made sense! i've posted the table defs for these tables at the
> bottom of this message.
> My problem is when using the following stored procedure query to
> search for orders based on different criteria, included a reference
> number for a book in the order, the join with the books table is
> causing cancelled orders not to be returned. For example, if i search
> for active order 1, because one of the possible search criteria is
> book reference, the Books table is joined to the query. Because order
> 1 is active, the book records in the Books table have orderID's
> stored, and the order is returned. However, if i search for cancelled
> order 2, because the books are lacking the OrderID field, the join is
> causing the order record not to be returned. I have tried using an
> INNER JOIN and a LEFT OUTER JOIN, and neither help. The ideal
> situation would be if the join for the Books table could be
> conditional on the OrderStatusID field in the Orders table, so that
> the query would be joined to the Books table if the order was active,
> and if the order was cancelled, the join would change to the
> CancelledRefundedBooks table.
> An additional complication is that if the join was to the
> CancelledRefundedBooks table, in the event of a cancelled order, the
> CancelledRefundedBooks table holds only the book reference numbers.
> This means that table would then need a further join onto the Books
> table to retrieve author, title etc. fields.
> The stored proc as it stands is as follows:
> ALTER PROCEDURE sp_searchorders
> @.refnumber int = NULL,
> @.surname nvarchar(100) = NULL,
> @.orderid int = NULL,
> @.postcode nvarchar(50) = NULL,
> @.booktitle nvarchar(500) = NULL
> WITH RECOMPILE
> AS
> SELECT DISTINCT
> Orders.OrderID, Staff.Surname AS StaffSur,
> Staff.FirstName AS StaffFir, Orders.CustomerID, Customers.Forenames,
> Customers.Surname,
> Customers.Telephone, Customers.Email,
> Customers.Forenames, Customers.Surname, Customers.TownCity,
> Websites.Name AS Website, Orders.OrdDate
> FROM Orders INNER JOIN
> Customers ON Orders.CustomerID => Customers.CustomerID INNER JOIN
> Staff ON Orders.StaffID = Staff.StaffID INNER
> JOIN
> Websites ON Orders.WebsiteID => Websites.WebsiteID LEFT OUTER JOIN
> Books ON Orders.OrderID = Books.OrderID
> WHERE CASE @.refnumber
> WHEN 0 THEN @.refnumber
> ELSE Books.Ref
> END
> = @.refnumber
> AND Customers.Surname LIKE COALESCE(@.surname, '%')
> AND Books.Title LIKE COALESCE(@.booktitle, '%')
> AND Customers.Postcode LIKE COALESCE(@.postcode, '%')
> AND CASE @.orderid
> WHEN 0 THEN @.orderid
> ELSE Orders.OrderID
> END
> = @.orderid
> My sincerest apologies for posting such a long narrative about the
> problem, but I can't think of any other way of describing it! I have
> included the CREATE statements for all the mentioned tables below, and
> if i can provide any more information to help come up with a solution
> plz ask. I am a relative newbie at SQL server, so please be gentle!!
> Many thanks in advance
>
> James Currer
>
> CREATE TABLE [Books] (
> [Ref] [int] NOT NULL ,
> [Author] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Title] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [PlacePubDate] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [Keywords] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Catalogues] [nvarchar] (80) COLLATE Latin1_General_CI_AS NULL ,
> [Cost] [money] NULL ,
> [DealerCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Notes] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [Price] [money] NULL ,
> [OrderID] [int] NULL ,
> [Weight] [numeric](6, 3) NULL ,
> [ISBN] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Row] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Shelf] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [LocationID] [int] NULL ,
> [StatusID] [int] NULL ,
> [DealerID] [int] NULL ,
> [BoxID] [int] NULL ,
> [LastUpdatedBy] [int] NULL ,
> CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
> (
> [Ref]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [CancelledRefundedBooks] (
> [BookRef] [int] NOT NULL ,
> [RefundID] [int] NULL ,
> [OrderID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [Orders] (
> [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> [StaffID] [int] NOT NULL ,
> [CustomerID] [int] NOT NULL ,
> [OrdDate] [datetime] NULL ,
> [ShipVia] [int] NULL ,
> [WebsiteID] [int] NOT NULL ,
> [PackingID] [int] NULL ,
> [PaymentID] [int] NULL ,
> [InvoiceNumber] [int] NULL ,
> [InvoiceDate] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [OrderStatusID] [int] NOT NULL CONSTRAINT [DF_Orders_OrderStatusID]
> DEFAULT (4),
> [TotalPricePaid] [money] NOT NULL CONSTRAINT
> [DF_Orders_TotalPricePaid] DEFAULT (0),
> [CustomersOwnRef] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [ShippingPaid] [money] NOT NULL CONSTRAINT [DF_Orders_ShippingPaid]
> DEFAULT (0.00),
> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
> (
> [OrderID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO

Conditional Rollback

I have inherited a lot of SQL2005 TSQL, that has the following generic catch structure:

BEGIN CATCH
IF @.@.TRANCOUNT > 0 AND (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH

From BOL, @.@.TRANCOUNT returns the number of active transactions for the current connection and XACT_STATE()) is a scalar function that reports the transaction state of a session, indicating whether or not the session has an active transaction, and whether or not the transaction is capable of being committed.

My question is: Should the tests be OR'd rather than AND'd?

The line in question would then read:

IF @.@.TRANCOUNT > 0 OR (XACT_STATE()) <> 0

BOL says,

Both the XACT_STATE and @.@.TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @.@.TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.

ie,

Instead of using the @.@.Trancount you can always check the XACT_STATE. Bcs @.@.TRANCOUNT never tells you wheather you have to do Commit or rollback. It only gives the number of transaction begin.

But

XACT_STATE = 0 no transaction active

XACT_STATE = 1 active transaction available & can be writable & commitable/rollbackable -- Commit/rollback

XACT_STATE = -1 active transaction available & uncommitable -- Rollbacked

|||

Thankyou Manivannan,

The TSQL in question runs after an error within the try section has been caught. My question relates as whether the functions should be OR'd instead of AND'd. Possibly only one of them should be used.

|||

Ok now, My direct answer is XACT_STATE is more enough to use....(trustable). Need not have or/and only XACT_STATE is enough to validate the condition.

|||

If I may summerise

BEGIN CATCH
IF @.@.TRANCOUNT > 0 AND (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH
should become

BEGIN CATCH
IF (XACT_STATE()) <> 0 -- < Suggested simpler construction.
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH

Is this your recommendation?

|||Yes...Exactly..

Conditional Joins

Hi all,

I have 4 tables with the structure shown below

Main Table :

Create Table TestMain
(TestMainId INT , TestCompanyID INT )

Other Tables :

Create Table TestCompany1
(Id INT , TestCompanyID INT )

Create Table TestCompany2
(Id INT , TestCompanyID INT )

Create Table TestCompany3
(Id INT , TestCompanyID INT )

In this above tables.. I would have a record in the table TestMain and a entry for that specific record would be in any of the tables like TestCompany1,TestCompany2,TestCompany3

Sample Records :

In the table TestMain

1 1000
2 2000
3 3000
4 4000
5 5000
6 6000
7 7000

In the table TestCompany1

1 1000
2 6000

In the table TestCompany2

1 3000
2 4000
3 5000

In the table TestCompany3

1 7000

How do I join those tables and fetch the main record with its subsequent entry from the other tables ?

Thanks in advance,

HHADo you have defined any relationship between the tables..? It's basic requirement for data integrity.

Anyway you can join the two tables this way...

Select Testmain.TestMainId, TestMain.TestCompanyID From TestMain
JOIN TestCompany1 ON TestMain.TestCompanyID = TestCompany1.TestCompanyID

You can join more than two tables using different join types...|||Hi

You would need to use left joins and maybe COALESCE but it is hard to know without more details. The fact that you are doing this hints that your design may not be sound too (although it may be - this looks like a mock up yes?).

HTH|||I think your best bet would be to inner join against each table and UNION or UNION ALL the results - your design does look dubious though, why are you segmenting companies across 3 tables - do they have different attributes per collection or is there another reason?|||Hi all,

There is a main table say COMPANY and they other tables CompanyA , CompanyB , CompanyC.

The main table COMPANY has the general info about the company ( like address , contact info) and there are 3 BIT columns to indicate what all type of company it falls under.If it falls under A & B , then the relevant information
are stored in CompanyA & CompanyB.

Now I need to write a proc which gets few input parameters and searches
for the company details.

1. If no parameters where passed , I need to get all the company from the
COMPANY with relevant information from the CompanyA, CompanyB,CompanyC.

2.If I get a parameter which says I should fetch only companys falling under
CompanyA , I should be able to get them too.

Still , the DB is in production , I cant touch the design.

Thanks for all your help ,

HHA|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71565

Terrible design - I'm sure you know or are at least becoming aware of.

Playing with post three from the link (not efficient - there may be better solutions):

CREATE TABLE #TESTMAIN
(TESTMAINID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY1
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY2
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY3
(ID INT , TESTCOMPANYID INT )

INSERT #TESTMAIN
SELECT 1, 1000 UNION ALL
SELECT 2, 2000 UNION ALL
SELECT 3, 3000 UNION ALL
SELECT 4, 4000 UNION ALL
SELECT 5, 5000 UNION ALL
SELECT 6, 6000 UNION ALL
SELECT 7, 7000

INSERT #TESTCOMPANY1
SELECT 1, 1000 UNION ALL
SELECT 2, 6000

INSERT #TESTCOMPANY2
SELECT 1, 3000 UNION ALL
SELECT 2, 4000 UNION ALL
SELECT 3, 5000

INSERT #TESTCOMPANY3
SELECT 1, 7000

DECLARE @.CompanyOneOnly AS Bit
SET @.CompanyOneOnly = 1

SELECT *
FROM -- Relvent companys
(SELECT X1.TESTMAINID,
X1.TESTCOMPANYID,
ISNULL(X2.ID,0) AS Comp1,
ISNULL(X3.ID,0) AS Comp2,
ISNULL(X4.ID,0) AS Comp3
FROM #TESTMAIN X1
LEFT JOIN #TESTCOMPANY1 X2 ON X1.TESTCOMPANYID = X2.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY2 X3 ON X1.TESTCOMPANYID = X3.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY3 X4 ON X1.TESTCOMPANYID = X4.TESTCOMPANYID) AS DerT
WHERE CAST(Comp1 AS Bit) = @.CompanyOneOnly OR @.CompanyOneOnly = 0

DROP TABLE #TESTMAIN
DROP TABLE #TESTCOMPANY1
DROP TABLE #TESTCOMPANY2
DROP TABLE #TESTCOMPANY3

Conditional computing

Hi everyone,
I'm working on a report that gives a summary of data in a sql server
database. The structure of the database is the following:
A company has one to many investments. These investments are for a certain
sector, are of a certain nature and are in a certain state. This gives us a
data model that ressembles this:
tblInvestments has a foreign key for the company, the sector and the state
tables.
Now, the report I have to do is a detail of the investments by sector. For
example, say we have "Primary", "Secondary", and "Manufacturing" as sectors,
the report must look like this:
Total investments for company x : 15
Sector Investments Active Total
invested
Primary 4 3 130
000$
Secondary 3 3 250
000$
Manufacturing 8 6 140
000$
The Investments column is simply a count of the investments for a particular
company. The Active column lists the count of all the investments that are
in the state "Active" and the Total invested is the sum of a field in the
Investments table for only the investments that are active. I've been
struggling for this problem for a while now and I'd like some input. Is
there a way to:
1) Do this in 1 view
2) List all the sectors even if there is no investments (to list a zero for
the other columns)
I tried to do two separate views for the simple total of the investments and
for the count of the investments and the total amount, but I can't get them
back together in one query (one line for each investment).
Any help would be appreciated.
ric.
hi eric,
It would have been more easier to give you solution, if you would have
posted sample table structure and data alongwith expected result set.
however on the basis of some information provided by you , the query given
in following example might be what you are looking for.
--sample data
create table tblInvestments (companyid int, sectorid int,
stateid int,
status varchar(10),
amount int)
go
create table company (companyid int primary key, companyname varchar(500))
go
create table sector(sectorid int primary key, sectorname varchar(500))
go
create table state(stateid int primary key, statename varchar(500))
go
insert into company values(1,'company1')
insert into company values(2,'company2')
insert into company values(3,'company3')
go
insert into sector values (1,'primary')
insert into sector values (2,'secondary')
insert into sector values (3,'manufacturing')
go
insert into state values (1,'CA')
insert into state values (2,'NJ')
insert into state values (3,'MA')
go
insert into tblinvestments values(1,1,1,'active',1000)
insert into tblinvestments values(1,1,1,'inactive',1000)
insert into tblinvestments values(2,1,1,'active',1000)
insert into tblinvestments values(1,2,1,'active',2000)
insert into tblinvestments values(2,2,1,'active',2000)
go
--required query
select b.companyname ,a.sectorname,
sum (case when c.companyid is null then 0 else 1 end ) 'investment' ,
sum(case c.status when 'active' then 1 else 0 end) 'active',
sum(case c.status when 'active' then c.amount else 0 end) 'total invested'
from sector a cross join company b
left outer join tblInvestments c
on a.sectorid = c.sectorid and b.companyid = c.companyid
group by a.sectorname, b.companyname
order by 1,2
compute sum (sum (case when c.companyid is null then 0 else 1 end )) by
b.companyname
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Concurrency issues with "Tree" structures.

Hi,

I'm currently implementing a database with a tree structure in a table. The
nodes in the tree are stored as records with a column called "Parent". The
root of the tree has a "NULL" parent. The path to each node is stored in
the column "Path" and is of the form "\000001\000002\000003\" etc. The
latter enabling me to fetch subtrees using the "LIKE" predicate. I also
have created the relation "ID" <-> "ID_Parent, effectively the table is
related to itself. I did this so that an attempt to remove a parent when
that parent still has children will fail due to referential integrity.
Unfortunately, in order to delete subtrees, I have to first set all of the
nodes in the subtree to point to the "NULL" parent so that I don't get
caught with integrity errors during deletes.

Unlike a typical linear system, any given node record is related to more
than one of the other records. What I mean is it is possible to follow a
chain from any given node back to the root (ancestors) or collect a series
of branches and leaves (descendants) and so it is not reasonable to consider
any given node in isolation. Changes to any given descendant can trigger
changes which are propagated to it's ancestors. I am not using recursion to
do this, rather, I am using an iterative approach to select and update the
parent, then the parents parent, etc. according to it's new state (given by
the state of it's immediate descendants), right back up to the root.

Now, the problem comes when I consider concurrency with respect to this
scheme. It seems to me, that locking the record I am updating is not
sufficient to ensure clients are kept synchronised or the integrity of the
tree structure is correct. I think I need to lock all ancestors of the tree
(HOLDLOCK) before performing any operation on a given node. Is this
reasonable? Also, consider the "delete" problem given above. I really
should HOLDLOCK on the entire subtree of any node I wish to delete as I am
going to set the entire subtrees parent values to NULL. I don't want
another client to perform a read on part of the subtree while the nodes are
"parentless" pending deletion.

Secondly, I am not sure how to handle synchronization of the tree for each
client. How does each client know when a change has been made to the tree?
Consider a client holding a copy of the tree in memory. Another client
deletes a subtree. The first client attempts to update one of the deleted
subtree nodes and fails because the node no longer exists. At this point,
in the eyes of the first client, all of the ancestors and all of the
descendants of the node in question must become suspect. Should the
software now attempt to re-build this part of the tree? It seems that any
operation on any of the nodes in the tree will potentially make a lot of
other nodes suspect and so my application will be doing a lot of updating.

I would be interested to hear any insights people have on these issues,
particularly those implementing a "file system" structure in their database
or similar. How do you deal with these concurrency issues when manipulating
trees?

Thanks

RobinI believe that Joe Celko has a book that is devoted to tree structures
in a SQl environment. Do a search on Amazon for Celko and you should
see it. I'm sure that Joe will probably chime in here as well. My
thoughts on the subject though...

> The path to each node is stored in the column "Path" and is of the
form
> "\000001\000002\000003\"
Ack, yuck, ick! I hate that method of storing tree information myself.
Check Joe's book for several different ways to store trees in a RDBMS.

> also have created the relation "ID" <-> "ID_Parent, effectively the
table is
> related to itself. I did this so that an attempt to remove a parent
when
> that parent still has children will fail due to referential
integrity.
> Unfortunately, in order to delete subtrees, I have to first set all
of the
> nodes in the subtree to point to the "NULL" parent so that I don't
get
> caught with integrity errors during deletes.
So, what you seem to be saying is that you want RI so that you can't
delete any rows by mistake, but you want to be able to easily delete
rows. You can't have it both ways... you either need to explicitly
delete the children (or set the FK's to NULL) or you need to remove the
RI.

> Changes to any given descendant can trigger changes which are
propagated to it's
> ancestors
This sounds like a design problem with regards to normalization to me,
but without knowing the specifics I really can't say.

As for the client application tracking changes to the tree, that is
really dependent on the business requirements for your application. You
can reload the client every X seconds or you could wait for the client
to take an action then check to see if the action is still valid on the
tree in its current form. To limit the number of client refreshes that
you have to perform, each node could use a last_updated datetime column
to track changes and you could retrieve only those nodes and their
subtrees where the last_updated column has a value that is greater than
the last time that you refreshed your tree.

My suggestion would be to check out Joe's book (or Google past posts by
Joe) to find alternative tree representations. Some of them are rather
ingenious and easy to work with.

HTH,
-Tom.|||Robin Tucker (idontwanttobespammedanymore@.reallyidont.com) writes:
> Now, the problem comes when I consider concurrency with respect to this
> scheme. It seems to me, that locking the record I am updating is not
> sufficient to ensure clients are kept synchronised or the integrity of
> the tree structure is correct. I think I need to lock all ancestors of
> the tree (HOLDLOCK) before performing any operation on a given node. Is
> this reasonable?

UPDLOCK would be better. Else you could run into conversion deadlocks.
An UPDLOCK is a shared lock, so other processes can read. But only one
can have an UPDLOCK.

> Also, consider the "delete" problem given above. I
> really should HOLDLOCK on the entire subtree of any node I wish to
> delete as I am going to set the entire subtrees parent values to NULL.
> I don't want another client to perform a read on part of the subtree
> while the nodes are "parentless" pending deletion.

I'm not really sure why you need to do this set NULL thing. In fact
that is something I would avoid like the plague. But then I know
very little of your actual business problem.

I think Joe Celko's trick for tress is to number each node in a way so
that a subtree is a contiguous range. Then you can blow away to whole
subtree in one delete.

Then again, you already had that path. Would not:

DELETE tbl WHERE path = 'a/b/c' or path LIKE 'a/b/c/%'

work?

Of course, even with set-based statements, you can have interesting
effects if two clients are it at the same time.

> Secondly, I am not sure how to handle synchronization of the tree for
> each client. How does each client know when a change has been made to
> the tree?

You will have to ask you tech lead about that. :-) Seriously, with
no knowledge of the requirements etc, it is very difficult to answer.
As Thomas said, you can refresh automatically with some frequency.
For more bells and whistle you could push the change by activating
some signaling mechanism from a trigger. But you make have to ask for
a bigger budget to do this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I would be interested to hear any insights people have on these
issues, .. <<

Get a copy of TREES & HIERARCHIES IN SQL and you can see several
different approaches.

>> in order to delete subtrees, I have to first set all of the nodes in
the subtree to point to the "NULL" parent so that I don't get caught
with integrity errors during deletes. <<

Look up the nested sets model; this one atomic DELETE FROM statement.|||Hi Tom,

> Ack, yuck, ick! I hate that method of storing tree information myself.
> Check Joe's book for several different ways to store trees in a RDBMS.

Ok, I don't want to get into a religious war about the best representation
of trees. I am aware of Celkos nested sets model but prefer the clarify of
the path string approach (my system isn't going to be operating with 1,000
users or anything, so I don't mind if it's performance isn't so good); of
course when I refer to clarity, I mean my own personal ability to visualise
whats going on and write code to perform the various manipulations required!
However, I will look once again at Celkos methods because I rejected them a
year ago for various reasons (the potential renumbering of large numbers of
nodes during inserts for example) and am now a bit wiser. I also tried out
nested sets with Tropashenkos' (apologies if I misspelled the name)
materialised path (binary fractions) and found this to be unbelievably slow
and restrictive (for various reasons mainly based on numeric accuracy).

> So, what you seem to be saying is that you want RI so that you can't
> delete any rows by mistake, but you want to be able to easily delete
> rows. You can't have it both ways... you either need to explicitly
> delete the children (or set the FK's to NULL) or you need to remove the
> RI.

You are right of course, I need to throw away the RI on this, then deletion
of subtrees will be atomic and I will not have to set parents to NULL.

>> Changes to any given descendant can trigger changes which are
> propagated to it's
>> ancestors
> This sounds like a design problem with regards to normalization to me,
> but without knowing the specifics I really can't say.

I will solve this by locking the ancestors (as another person says in his
post, using UPDLOCK rather than HOLDLOCK) and then processing them in any
transaction that changes state. Thinking about it though, it will still be
possible for a client to read a node that has not yet been processed while
another client is currently processing. So that first client may well
receive some processed and some unprocessed nodes back after a read
operation (is this true though? If the processing occurs within a
transaction, can other users read the uncommitted data?). The reason for
this is because the algorithm for state propagation cannot be atomic (as
fetching "ancestors" of any given node requires iteration).

However, I suppose it is possible for the client to detect problems like
this, by checking ancestors itself for consistency and forcing a refresh if
it detects an inconsistency when it reads back parts of the tree. Extra
work I think but bearing in mind the above (Celko nested sets), I suppose it
may be possible to UPDATE all ancestors atomically. If this is indeed the
case, then I might not need an iterative approach. But I doubt this is
possible, as I would have to ensure updates on the set of ancestors were
performed in order of their "depth" (leaves first of course). I don't think
SQL has such a mechanism (tables are sets after all, not sequences). I
would love to know how, using a path approach it is possible to fetch all
ancestors of a given node. I don't think it can be done with just a path
string and ID_Parent relation.

The specifics of this is each node has a "condition" (green, yellow, red,
undefined) according to the condition of it's children. For nodes of type
0, it's condition is the highest condition of each of it's children, for
nodes of type 1, it's condition is the condition of it's "most recently
added child". For nodes of type 2, the condition is fixed by the client
when the record is created. Condition propagation basically is tasked with
ensuring these conditions are consistent after deletes, updates or inserts.

> As for the client application tracking changes to the tree, that is
> really dependent on the business requirements for your application. You
> can reload the client every X seconds or you could wait for the client
> to take an action then check to see if the action is still valid on the
> tree in its current form. To limit the number of client refreshes that
> you have to perform, each node could use a last_updated datetime column
> to track changes and you could retrieve only those nodes and their
> subtrees where the last_updated column has a value that is greater than
> the last time that you refreshed your tree.

I have another idea about this (I will timestamp the nodes). I have a
worker thread that serializes access to the database from the various client
controls, firing callbacks when it performs an operation. This thread can
check through the tree in it's idle time to ensure it is synchronized. Of
course, if I execute an update and fail (ROWCOUNT = 0) or a delete come to
think of it, I can force a refresh of the entire subtree and it's ancestors.
But I would consider the entire subtree and all it's ancestors to be suspect
if I detected any given node had a later timestamp than that expected.

> My suggestion would be to check out Joe's book (or Google past posts by
> Joe) to find alternative tree representations. Some of them are rather
> ingenious and easy to work with.
> HTH,
> -Tom.

Thanks for all of your ideas.

Robin