SELECT in the Join clause

  • Thread starter Thread starter Brian
  • Start date Start date
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
 
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
 
Works great. Thanks. But I have another question, why
doesn't this type of statement work in Interbase.
 
Don't have a clue, since I don't use Interbase. Guess that Interbase doesn't
know the Abs function (VBA)?
 
Back
Top