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)