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)?
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

perform INNER JOIN on query results 2
Query with "In" in the WHERE clause isn't updateable 1
Multi-level Group By Clause is not allowed in a subquery 4
Syntax error in FROM clause 3
Issues with Left Join 1
Combine 2 queries into 1 1
how to use where clause in my query 5
Joining Multiple Select statements 1

Back
Top