467,926 Members | 1,438 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,926 developers. It's quick & easy.

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:
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
  • viewed: 1294
Share:
3 Replies
ck9663
Expert 2GB
Could you post some sample data coming from the three tables?

-- CK
Oct 23 '08 #2
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
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.

Similar topics

5 posts views Thread by Ciar?n | last post: by
4 posts views Thread by sdowney717 | last post: by
2 posts views Thread by James | last post: by
1 post views Thread by bbdobuddy | last post: by
4 posts views Thread by Rnt6872 | last post: by
3 posts views Thread by dotNetDummi | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.