Joining Statements in a Query

[nickvans]

Hello everyone,

I am new to SQL Server, and am having trouble joining several SELECT statements together.

I have three SQL Select statements that do separate count operations to get the status of many projects. (One statement counts the number of open projects, one counts the number of completed projects, and the third one counts the number of late projects.) The count operations use GROUP BY project to operate.

An example of one of these queries is:

    -- open
    SELECT PROJECT, COUNT(*) AS OPENAI
    FROM dbo.ACTION_ITEM sel_open
    WHERE (ACTUAL_DATE IS NULL) AND (DUE_DATE >= GETDATE()) 
    GROUP BY PROJECT

 -- complete
    SELECT PROJECT, COUNT(*) AS COMPLETEAI
    FROM dbo.ACTION_ITEM sel_comp
    WHERE (ACTUAL_DATE IS NOT NULL)
    GROUP BY PROJECT

I would like to create a query that joins these three select statements together so my result has the form

PROJECT | OPEN | CLOSED | LATE
projectA | 3 | 1 | 2
projectB | 4 | 6 | 3

etc.

I have tried:

SELECT PROJECT, OPENAI
FROM sel_comp LEFT JOIN sel_open
ON sel_comp.PROJECT= sel_open.EFFECTIVITY

but it comes back with a MSG 208, Level 16, State 1, Line 21
"Invalid object name 'sel_all' " and is unhappy with the FROM statement.

Can anyone tell me what I'm doing wrong?

Thanks

nickvans

[ck9663]

Could you post some sample data coming from the three tables?

-- CK

[nickvans]

Could you post some sample data coming from the three tables?

-- CK

Thanks for taking a look for me ck.

The data in each of the three queries looks like

PROJECT varchar(50)
OPENAI int

and has data that looks like

PROJECT | OPENAI
Bill, 3
John, 1
NULL, 1
Phil, 2

Similarly the two other queries have columns (PROJECT | CLOSEDAI) and (PROJECT | LATEAI) with the same types of data in them.

I think basically what I'm trying to do is avoid creating three views and joining them together. I'd prefer to just have one view that joins three subqueries together.

Thanks again.

[nickvans]

Well, yet again I figured it out after posting the question. The secret (a very well publicized secret if you know to look for it) is to create a table variable, then select from that.

Irritatingly, you have to give the table variable an alias to use a where clause, but such is the microsoft way.

Thanks for looking.