|
|
Posted by wbpelen on 10/11/06 19:42
Alex,
Let me update my previous post.
The subselects within the join are not necessary.
It is the NOT EXISTS that you should be interested in.
You will still have to join the table with itself to filter out the
rows for VO and AD within two separate aliased tables.
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.
Navigation:
[Reply to this message]
|