calculate status based on other records

I am trying to retrieve the order status of a record based on the existence
of other records, but I am having difficulty. Below is a sample of what I'm
trying to accomplish. Any help is appreciated.
To determine if an order event is returnable, I need to find if there are
any return order records for which the order event is the parent. Given the
table and data defined below, the desk is not returnable, but the hutch is
returnable. I would like to return a record set of all order events that
provides the id, type, description, and whether it is returnable, but I have
been unable to create such a query. I could also create a new field for
flag its return status, but there are several other similar situations and I
'd
prefer not to have that many extra fields (although not sure which approach
would work best).
create table [dbo].[events] (
[id] [int] NOT NULL,
[type] [varchar] (20) NOT NULL,
[description] [varchar] (50) NOT NULL,
[parent] [int] NULL
)
insert into events (id, type, description, parent) values (1, 'order',
'desk', null)
insert into events (id, type, description, parent) values (2, 'order',
'hutch', null)
insert into events (id, type, description, parent) values (3, 'return
order', 'desk', 1)"John" <someone@.nowhere.com> wrote in message
news:O0oXLkLEFHA.3976@.tk2msftngp13.phx.gbl...
> I am trying to retrieve the order status of a record based on the
existence
> of other records, but I am having difficulty. Below is a sample of what
I'm
> trying to accomplish. Any help is appreciated.
> To determine if an order event is returnable, I need to find if there are
> any return order records for which the order event is the parent. Given
the
> table and data defined below, the desk is not returnable, but the hutch is
> returnable. I would like to return a record set of all order events that
> provides the id, type, description, and whether it is returnable, but I
have
> been unable to create such a query. I could also create a new field for
> flag its return status, but there are several other similar situations and
I'd
> prefer not to have that many extra fields (although not sure which
approach
> would work best).
> create table [dbo].[events] (
> [id] [int] NOT NULL,
> [type] [varchar] (20) NOT NULL,
> [description] [varchar] (50) NOT NULL,
> [parent] [int] NULL
> )
> insert into events (id, type, description, parent) values (1, 'order',
> 'desk', null)
> insert into events (id, type, description, parent) values (2, 'order',
> 'hutch', null)
> insert into events (id, type, description, parent) values (3, 'return
> order', 'desk', 1)
>
Hello -
If I understand correctly, the desk is not returnable because it has already
been returned. So, something like this should give you a list of all the
returnable items:
SELECT id
FROM events
WHERE NOT EXISTS (SELECT id FROM events e2 WHERE e2.parent=events.id AND
e2.type='return order')
Regards,
Simon|||"Simon Shearn" <simon@.antispam.aardvarks> wrote in message
news:420d8e46$0$38040$bed64819@.news.gradwell.net...
> "John" <someone@.nowhere.com> wrote in message
> news:O0oXLkLEFHA.3976@.tk2msftngp13.phx.gbl...
> existence
> I'm
> the
> have
> I'd
> approach
> Hello -
> If I understand correctly, the desk is not returnable because it has
> already
> been returned. So, something like this should give you a list of all the
> returnable items:
> SELECT id
> FROM events
> WHERE NOT EXISTS (SELECT id FROM events e2 WHERE e2.parent=events.id AND
> e2.type='return order')
> Regards,
> Simon
>
Thanks for you response Simon.
It is not exactly what I was looking for. I don't simply want a list of
returnable items. I want a list of all orders (whether returned or not) and
their returnable status. So for the data above, I'd like the following data
returned
id type description returnable
1 'order' 'desk' no
2 'order' 'hutch' yes|||On Sat, 12 Feb 2005 07:50:30 -0500, John wrote:
(snip)
>It is not exactly what I was looking for. I don't simply want a list of
>returnable items. I want a list of all orders (whether returned or not) an
d
>their returnable status. So for the data above, I'd like the following dat
a
>returned
>id type description returnable
>1 'order' 'desk' no
>2 'order' 'hutch' yes
Hi John,
SELECT id, type, description,
CASE WHEN EXISTS (SELECT id
FROM events AS e2
WHERE e2.parent = events.id
AND e2.type='return order')
THEN 'no'
ELSE 'yes'
END AS returnable
FROM events
WHERE type = 'order'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

c# 2005 express edition install difficulty

I just installed express and found out that it won't connect to any of our
production Sql Server 2000 data or Sql Server 2003 MSDE (my local
development db). Another one of our developers installed Sql Server 2005
express with the c# 2005 express and couldn't connect to his production box
(also SS 2000) and screwed up Enterprise manager.
Has anyone else had this problem'Peter
> express with the c# 2005 express and couldn't connect to his production
> box (also SS 2000) and screwed up Enterprise manager.
>
Any errors?
"Peter Lux" <pete.lux@.theheritagegrp.com> wrote in message
news:ubejqfDSGHA.4608@.tk2msftngp13.phx.gbl...
>I just installed express and found out that it won't connect to any of our
>production Sql Server 2000 data or Sql Server 2003 MSDE (my local
>development db). Another one of our developers installed Sql Server 2005
>express with the c# 2005 express and couldn't connect to his production box
>(also SS 2000) and screwed up Enterprise manager.
> Has anyone else had this problem'
>|||The default setup of Express Edition does not enable network connection from
off the box where it is installed. Have you enabled network connections and
opened any firewalls to allow connections?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter Lux" <pete.lux@.theheritagegrp.com> wrote in message
news:ubejqfDSGHA.4608@.tk2msftngp13.phx.gbl...
>I just installed express and found out that it won't connect to any of our
>production Sql Server 2000 data or Sql Server 2003 MSDE (my local
>development db). Another one of our developers installed Sql Server 2005
>express with the c# 2005 express and couldn't connect to his production box
>(also SS 2000) and screwed up Enterprise manager.
> Has anyone else had this problem'
>