Subquery

debbiecoates · June 18th, 2008, 04:49 AM

if I have a table of Projects (projectid, projectname) and another table of projectperiods (Projectid, Period, from, to). a project can have many periods.

eg
PROJECTS
projectid, projectname
101 The Little House Project
102 The big House project

PROJECTPERIODS
Periodid, Projectid, period, from, To
1 101 1 01/01/08 01/03/08
2 102 1 01/01/08 30/04/08
3 101 2 02/03/08 30/05/08
4 101 3 01/06/08 30/07/08

I want to be able to select projectid and project name from projects and the maximum period from projectperiods.

In order to do this i have made a view out of the project periods grouping by the projectid, selecting the max of the period.

then creating another view and joining the two.

I am not too sure of SQL, but I think i can do this with one view using a subquery, so i would select projectid, projectname, max(project) from project .... and then I get confussed. can anyone help me please, or point me to a tutorial of some sort explaining this.

cheers debbie

debbiecoates · June 18th, 2008, 05:05 AM

i am playing with this, but it doesn't seem to work

SELECT *projectid
FROM dbo.PROJECT
WHERE (ProjectID =
                          (SELECT MAX(period)
                            FROM projectperiod
                            WHERE projectid = project.projectid))

joefawcett · June 18th, 2008, 07:09 AM

Code:

SELECT
  P.ProjectId,
  P.ProjectName,
  PP.PeriodId,
  PP.Period,
  PP.[From],
  PP.[To]
FROM
  Projects P INNER JOIN ProjectPeriods PP
  ON P.ProjectId = PP.ProjectId INNER JOIN
  (SELECT ProjectId, MAX(Period) MaxPeriod FROM ProjectPeriods GROUP BY ProjectId) MP
  ON P.ProjectId = MP.ProjectId AND PP.Period = MP.MaxPeriod;

--

Joe (Microsoft MVP - XML)

Old Pedant · June 18th, 2008, 03:25 PM

As a minor comment...

If you don't need all the fields from ProjectPeriods that joefawcett provided in his answer--if you indeed need only the fields you noted in your first message ("I want to be able to select projectid and project name from projects and the maximum period from projectperiods"), then you can simplify that query a bit:

Code:

SELECT P.ProjectId, P.ProjectName, MP.MaxPeriod
FROM Projects P,
     ( SELECT ProjectId, MAX(Period) AS MaxPeriod 
       FROM ProjectPeriods 
       GROUP BY ProjectId
     ) AS MP
WHERE P.ProjectId = MP.ProjectId

debbiecoates · June 25th, 2008, 03:49 AM

Thank you very much

Debbie x