Solved

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

Posted on 2007-04-03
4
169 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

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…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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