By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,576 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,576 IT Pros & Developers. It's quick & easy.

Joining Statements in a Query

P: 62
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:
Expand|Select|Wrap|Line Numbers
  1.     -- open
  2.     SELECT PROJECT, COUNT(*) AS OPENAI
  3.     FROM dbo.ACTION_ITEM sel_open
  4.     WHERE (ACTUAL_DATE IS NULL) AND (DUE_DATE >= GETDATE()) 
  5.     GROUP BY PROJECT
Expand|Select|Wrap|Line Numbers
  1.  -- complete
  2.     SELECT PROJECT, COUNT(*) AS COMPLETEAI
  3.     FROM dbo.ACTION_ITEM sel_comp
  4.     WHERE (ACTUAL_DATE IS NOT NULL)
  5.     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:

Expand|Select|Wrap|Line Numbers
  1. SELECT PROJECT, OPENAI
  2. FROM sel_comp LEFT JOIN sel_open
  3. 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
Oct 22 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
Could you post some sample data coming from the three tables?

-- CK
Oct 23 '08 #2

P: 62
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.
Oct 23 '08 #3

P: 62
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.
Oct 23 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.