A new question.

I have a school history table: ID_HISTORY, ID_STUDENT, ID_SCHOOL, DATE_CREATED.

When I give a date like 01/01/2007 up in the query, the query need to search the records in the school history table where the id from the students is null. And when the id is null it need to find the id from the student from a record with the same id from the school and a date that is less but higher than the other record I gave up.

I already tried to do this but with no luck. It always give me a error.

SELECT A.ID_HISTORY, A.ID_STUDENT, A.ID_SCHOOL, A.DATE_CREATED FROM SCHOOL_HISTORY A WHERE A.DATE_CREATED =
(
SELECT MAX(B.DATE_CREATED) FROM SCHOOL_HISTORY B WHERE B.ID_SCHOOL = A.ID_SCHOOL AND CAST(B.DATE_CREATED AS DATE) <= '01/01/2007' AND
(
SELECT C.ID_STUDENT FROM SCHOOL_HIST C WHERE CAST(C.DATE_CREATED AS DATE) = '01/01/2007' AND C.ID_SCHOOL = A.ID_SCHOOL
)
IS NULL
)

Sorry for my bad English

Thanks in advance

Nyh

A new question. See post above.

I do not understand what you want. Maybe if you give an example of the data in the table and what the query should produce.


Andy

Do you mean something like

SELECT DISTINCT A.ID_HISTORY , A.ID_SCHOOL , A.DATE_CREATED, B.ID_STUDENT
FROM
SCHOOL_HISTORY A ,
SCHOOL_HISTORY B
WHERE A.ID_STUDENT IS NULL
AND A.ID_SCHOOL = B.ID_SCHOOL
AND B.DATE_CREATED > A.DATE_CREATED
AND B.DATE_CREATED < your-given-date
AND B.ID_STUDENT IS NOT NULL;



You want to find records where the student Id is null
WHERE A.ID_STUDENT IS NULL
and for those records you want to find rows with the same school id
AND A.ID_SCHOOL = B.ID_SCHOOL
and a date that is less [than the specified date]
AND B.DATE_CREATED < your-given-date
but higher the date of the other record given
AND B.DATE_CREATED > A.DATE_CREATED
and, of course, the student id should be given in that record
AND B.ID_STUDENT IS NOT NULL