Help with sql query on the same table

My table (ProjectList) columns are: ProjectId, MainProjectId, SecondaryProjectId, ProjectName, EndDate

I need to write a query that brings me all the rows from this table where

EndDate <= 40394 and for each ProjectId i need to bring again all the rows where

(MainProjectId = ProjectId)OR(SecondaryProjectId = ProjectId)

Example:

1, 0,开发者_JAVA百科 0, "project1", 54789

2, 1, 0, "project2", 54789

3, 1, 2, "project3", 40393

The query should return all the 3 rows (the third one meets the condition of the date and the other 2 rows meets the condition where they are in MainProjectId and SecondaryProjectId of project3)

Thanks.

I guess this is what you are looking for -

  select * from ProjectList where EndDate <= 40394 or
    mainProjectID in (
    select mainProjectID from projectList where EndDate <= 40394
    ) or secondaryProjectID in (
    select secondaryProjectID from projectList where EndDate <= 40394
    )