in need of subquery help

[Guest]

Here's my table (MyTable):
employee_id dependant_id effective_date last_name first_name
12345 0 1/1/1999 Jones
Robert
12345 1 1/1/1998 Jones
Jennifer
12345 2 1/1/2001 Jones
Amanda
54321 0 6/12/1997 Williams
Carl
77878 4 5/5/2004 Johnson
Tracy

dependant_id=0 means they are an employee
dependant_id <> 0 means they are a dependant of an employee

I am trying to write a query that will search the entire table and return
the rows where a dependants effective date is BEFORE it's matching employee
effective date OR if a dependant does NOT have a matching employee.
The example above should return Jennifer Jones and Tracy Johnson.
---I am ok with using two queries, just need help on how to write them.
Thanks

 

[Marshall Barton]

Here's my table (MyTable):
employee_id dependant_id effective_date last_name first_name
12345 0 1/1/1999 Jones
Robert
12345 1 1/1/1998 Jones
Jennifer
12345 2 1/1/2001 Jones
Amanda
54321 0 6/12/1997 Williams
Carl
77878 4 5/5/2004 Johnson
Tracy

dependant_id=0 means they are an employee
dependant_id <> 0 means they are a dependant of an employee

I am trying to write a query that will search the entire table and return
the rows where a dependants effective date is BEFORE it's matching employee
effective date OR if a dependant does NOT have a matching employee.
The example above should return Jennifer Jones and Tracy Johnson.
---I am ok with using two queries, just need help on how to write them.

Off the top of my head:

SELECT T.*
FROM MyTable As T
LEFT JOIN MyTable As X
ON T.[employee_id] = X.[employee_id]
WHERE T.[dependant_id] > 0
AND X.[dependant_id] = 0
AND (T.[effective_date] > X.[effective_date]
OR X.[employee_id] Is Null)