Joining Statements in a Query

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:

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:

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

re: Joining Statements in a Query

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

-- CK

re: Joining Statements in a Query

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.

re: Joining Statements in a Query

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.