ansaurus

Question

Answer 1

+2 A:

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
    )

Sachin Shanbhag 2010-08-04 09:00:39

That's not exactly right, because if a project doesn't have main/secondary then the values are 0, this query brings me everything but the mainProjects

Shay 2010-08-04 09:24:07

Consider this table:1, 0, 0, "project1", 403932, 0, 0, "project2", 547893, 2, 0, "project3", 403934, 2, 0, "project4", 547895, 2, 4, "project5", 40393The query misses project2.

Shay 2010-08-04 09:27:24

Not sure why it would miss Project2 in this case. The mainProjectID of Project2 matches with the Project1 which has enddate less than 40394. So I still get project2 in my results. Notice the OR condition.

Sachin Shanbhag 2010-08-04 09:31:04

I had to modify your query by adding another condition in each inner select (mainProject > 0) because otherwise and will just bring all the rows, here is the modified query:select * from ProjectList where EDate <= 40394 or MainProjectin ( select MainProject from projectList where EDate <= 40394 and MainProject > 0)orSecondaryProjectin ( select SecondaryProject from projectList where EDate <= 40394 and SecondaryProject > 0)what is wrong here? (again missed project 2)

Shay 2010-08-04 09:34:33

Can you explain, based on what condition do you want project2 to be included in the results?

Sachin Shanbhag 2010-08-04 09:46:15

Yes, project2 should be included if he is MainProject or SecondaryProject of any of the rows (no matter what is it's enddate).Actually this is general requirement, in my example, project4 isSecondaryProject and it's child is project5, therefore project4 is included no matter what is it's enddate.

Shay 2010-08-04 09:51:17

Ok, so accordingly my query will get you project2 in results. But u said you added condition where MainProject > 0. In that case how will you get Project2 in the resultset?. It does not satisfy the condition... Am I missing something here?

Sachin Shanbhag 2010-08-04 09:54:57

I'll explain my main goal, i'm building a tree from this table, leafs (mainproject=0, secondaryproject=0) should be there only if their enddate <= 40393,all other nodes should be included if their enddate <= 40393 OR if they are a parent of a leaf.I've added the > 0 because if i didn't put it, the result would justbe the all table since the inner select returns also the value 0.

Shay 2010-08-04 10:42:43

Correction: all other nodes are included if they are a parent of a leaf with enddate <= 40393 or if they are a parent of a parent of that leaf.in my example project4 is a parent and therefore it is added.project2 is parent of a parent and was not included in your query (which it should).

Shay 2010-08-04 10:45:11

ansaurus

tags:

views:

answers:

Help with sql query on the same table.

related questions