Solved

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

Posted on 2007-04-03
4
166 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]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks angellll for your help, DUH... I should have noticed that. Thanks again for your help> :)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 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

7 Experts available now in Live!

Get 1:1 Help Now