Solved

Getting a Subquery filed to be part of the main SELECT statement

Posted on 2007-04-03
4
168 Views
Last Modified: 2010-03-20
Hello and how are things? I believe this is not as hard as I am making it to be, but I want to find the best way to do this SELECT statemtent. What I have is this:

Select TOP 10 * from dm2_employee dm2E Where Date_of_Term IS NULL AND control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi') AND EXISTS (Select dm2EA.employee_number, MAX(dm2EA.activity_date) AS Effective_Date, dm2EA.Employee_Status from dm2_Emp_Activity dm2EA Group BY dm2EA.employee_number, dm2EA.activity_date, dm2EA.Employee_Status)

In the subquery, I need to make that MAX(dm2EA.activity_date) part of the data set in the main query. How do I go about doing that with this SELECT statement?

C
0
Comment
Question by:Corey_819
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18843399
I would guess some query like this following:

Select TOP 10 *
from dm2_employee dm2E
JOIN (Select dm2EA.employee_number, MAX(dm2EA.activity_date) AS Effective_Date, dm2EA.Employee_Status
from dm2_Emp_Activity dm2EA
Group BY dm2EA.employee_number, dm2EA.activity_date, dm2EA.Employee_Status) l
on <some condition>
Where Date_of_Term IS NULL
AND control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi')

however, as you had no joining condition in the exists subquery, you will have to put that yourself...
0
 
LVL 1

Author Comment

by:Corey_819
ID: 18843918
Thanks for your help and suggestion. One more quick question. I am receving a syntax error near the keyword ON. Thoguhts?

Select dm2E.Employee_number, dm2E.Employment_date, dm2E.Last_Name from dm2_employee dm2E JOIN (Select dm2EA.employee_number, MAX(dm2EA.activity_date) AS Effective_Date, dm2EA.employee_status from dm2_emp_activity dm2EA Group by dm2EA.employee_number, dm2EA.activity_date, dm2EA.Employee_Status) ON (dm2E.employee_number = dm2EA.employee_number) Where dm2E.Date_of_Term IS NULL AND dm2E.control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi')  
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 65 total points
ID: 18843948
you forgot to give the joined query an alias name, like I put an "l" behind the closing bracket:

Select dm2E.Employee_number, dm2E.Employment_date, dm2E.Last_Name, l.Effective_Date
from dm2_employee dm2E
JOIN (Select dm2EA.employee_number, MAX(dm2EA.activity_date) AS Effective_Date, dm2EA.employee_status from dm2_emp_activity dm2EA Group by dm2EA.employee_number, dm2EA.activity_date, dm2EA.Employee_Status
) l
ON (dm2E.employee_number = l.employee_number)
Where dm2E.Date_of_Term IS NULL
AND dm2E.control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi')

tip:
* format your queries like above, makes them much easier to read
0
 
LVL 1

Author Comment

by:Corey_819
ID: 18844075
Thanks angellll for your help, DUH... I should have noticed that. Thanks again for your help> :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sQL pivot 9 47
SQL Help joining two tables 7 48
How does this SELECT query work 11 100
Access 2010 Query Syntax 5 23
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now