SELECT in the Join clause

B

Brian

I am a newbie to the SQL, but I was wondering if the
statement below is valid. It doesn't work for me. The
query only uses one table. I wanted to join to Select
statements so I could COUNT a field ("IsComplete") when it
is false and when it is true and GROUP it by the userid.
Thanks. Any help would be useful.




SELECT CTTable.ownerid, CTTable.CompleteTrue,
CFTable.CompleteFalse
FROM
(SELECT task.ownerid, Count(task.ownerid) as CompleteTrue
FROM task WHERE task.tasktype = 2 and task.iscomplete= -1
GROUP BY task.ownerid) as CTTable
INNER JOIN
(SELECT task.ownerid, Count(task.ownerid) as CompleteFalse
FROM task WHERE task.tasktype = 2 and task.iscomplete= 0
GROUP BY task.ownerid) as CFTable
ON CTTable.ownerid = CFTable.ownerid
GROUP BY CTTable.ownerid
 
J

John Spencer (MVP)

TRY the following


SELECT Task.OwnerID,
Abs(SUM(IsComplete = -1)) as CompleteTrue,
Abs(SUM(IsComplete = 0)) as CompleteFalse
FROM Task
WHERE TaskType = 2
GROUP BY OwnerID
 
B

Brian

Works great. Thanks. But I have another question, why
doesn't this type of statement work in Interbase.
 
J

John Spencer (MVP)

Don't have a clue, since I don't use Interbase. Guess that Interbase doesn't
know the Abs function (VBA)?
 
Top