CODEWITHSUNDEEP
sqlsubquery
user1688175
user1688175

Reputation:

Subquery issue (SQL)

I am facing a challenge with a SQL query. Basically I would like to retrieve the registers which present a [Start] later than their respective [Outgoing tasks].

The table looks like this:

Main Project    Main Link    Name         Start            Outgoing tasks
A               1            A1           02.01.2012       A2   
A               1            A2           01.01.2012       A3

...

The query I wrote is this one:

SELECT [Name], [Start], [Outgoing tasks] 
FROM [Sheet1$] 
WHERE [Main project] = 'A' 
AND [Main link] = '1' 
AND [Outgoing tasks] IS NOT NULL 
AND [Start] > (SELECT [Start] 
        FROM [Sheet1$] 
        WHERE [Main project] = 'A' 
        AND [Main link] = '1' 
        AND [Name] =  [Outgoing tasks])

It doesn't return any error, however it simply doesn't bring the expected results.

Do you guys know what might be wrong? Any support is very appreciated!!!

Further info:

This table comes from a MS Project like application. So we have a milestone, its data, successor and predecessor. What I actually need is a list of milestones which have a start date later than its successor (Which is an error in the project management perspective). So if A1.Start > A2.Start, then i should appear in the results. Let me know if you need any further detail.

Upvotes: 0

Views: 108

Answers (2)

podiluska
podiluska

Reputation: 51514

Try

 select t1.* from [$Sheet1] t1
      inner join [$Sheet1] t2
           on t1.[main project] = t2.[main project]
           and t1.[main link] = t2.[main link]
           and t1.[outgoing tasks] = t2.name
           and t1.start>t2.start

Upvotes: 1

Paul Tregoing
Paul Tregoing

Reputation: 111

If this is meant to be a correlated sub-query, you need a way to refer to the outer statement's table inside the inner query. In MySQL you'd do it like this:

SELECT name, start, outgoing_tasks FROM sheet1 foo WHERE main_project = 'A' AND main_link = '1' AND outgoing_tasks IS NOT NULL AND start > (SELECT start FROM sheet1 bar WHERE main_project = 'A' AND main_link = '1' AND bar.name = foo.outgoing_tasks);

Notice that I give the outer query's table an alias of foo and the inner query's table the alias of bar. Then I can refer to the outer table in the WHERE predicate of the nested (inner) query: ... AND bar.name = foo.outgoing_tasks ...

Upvotes: 0

Related Questions