|
|
Posted by wbpelen on 10/11/06 19:30
Alex,
You seem to be almost there and you just have to put it all together to
come up with the answer.
To help you along, let's talk syntax and "NOT EXISTS"
1. Are you familiar with the syntax of a join that allows you to
select a subset of data before using it in the join.
As this applies to your case:
SELECT col1, col2, col3
FROM ( SELECT col1, col2, col3
FROM codes
WHERE code = '?' ) <tbl_alias>
INNER JOIN (SELECT col1, col2, col3
FROM codes
WHERE code = '?' ) <tbl_alias2>
ON tbl_alias1.col? = tbl_alias2.col?
< Add more join conditions >
2. NOT EXISTS is useful in your example. What do you want to NOT
EXIST?
> 3. although 'AD' does not come immediately before 'VO'
(in the
> case of ID_NUMBER 12) 'AT' cannot be found in between
AND NOT EXISTS ( SELECT 1 FROM codes WHERE ??? )
Remember in the N/E sub_select you will be comparing values in a
correlated sub_query.
I'll check back and post the answer if you are still having difficulty
but only after you post some sql that you have been trying to make
work.
alex wrote:
> Hello experts.
>
> I'm a novice sql writer and need some help in writing a query to
> extract applicable data from the following table (titled EMPLOYEE):
>
> --
> ID_NUMBER CODE DATE
> ------------------ --------- --------
> 12 VO 20060914
> 12 XD 20060913
> 12 AD 20060912
> 12 WR 20060911
> 12 AT 20060910
> 45 VO 20060914
> 45 XR 20060913
> 45 AT 20060912
> 45 AD 20060911
> 45 AT 20060910
> 78 AD 20060914
> 78 AT 20060913
> 78 VO 20060912
> 78 AD 20060911
> 78 AT 20060910
>
> I need to select ID_NUMBER
> from EMPLOYEE
> where CODE = 'VO'
>
> caveat: I only want the ID_NUMBER(s) where the CODE = 'VO'
> and the previous CODE (by DATE) = 'AD'
> or the previous CODE (by DATE) = 'AD' with any CODE in between
> except 'AT';
>
> E.g., in the above example, the appropriate code should select
> ID_NUMBER(s) 12 and 78 because
> 1. a VO code exists
> 2. an AD code (by DATE) precedes it
> 3. although 'AD' does not come immediately before 'VO' (in the
> case of ID_NUMBER 12) 'AT' cannot be found in between
>
> I hope I haven't confused anyone. Any help would be appreciated.
[Back to original message]
|