CODEWITHSUNDEEP
sqlms-accessjet
Matt Hall
Matt Hall

Reputation: 2412

Filtering an aliased column

I have the following query in MS-Access, which runs fine:

SELECT A1.LRN, A1.StartDate, A1.Destination, 
(
    SELECT TOP 1 A2.StartDate
    FROM testEnrolment As A2
    WHERE A2.LRN = A1.LRN AND A2.StartDate > A1.StartDate
    ORDER BY A2.StartDate
) As NextStartDate, 
(
    SELECT TOP 1 B2.Destination
    FROM testEnrolment As B2
    WHERE B2.LRN = A1.LRN AND B2.StartDate > A1.StartDate
    ORDER BY B2.StartDate
) As NextDestination
FROM testEnrolment As A1

The 2 aliased columns NextStartDate, NextDestination get their data from the StartDate and Destination fields of the next record for the current LRN.

So if the table testEnrolment has this data:

LRN    StartDate    Destination
--------------------------------
L0001  01/08/2014   Unemployed
L0001  02/08/2014   Education
L0001  03/08/2014   Unemployed
L0002  20/09/2014   Education
L0002  21/09/2014

The query will result in this:

LRN    StartDate   Destination  NextStartDate  NextDestination
--------------------------------------------------------------
L0001  01/08/2014  Unemployed   02/08/2014     Education
L0001  02/08/2014  Education    03/08/2014     Unemployed
L0001  03/08/2014  Unemployed
L0002  20/09/2014  Education    21/09/2014
L0002  21/09/2014

What I want to do next is filter the column alias NextDestination by records that are not equal to "Education".

A WHERE clause will not work on a column alias and I can't seem to get HAVING to work either.

Upvotes: 2

Views: 824

Answers (1)

JamieD77
JamieD77

Reputation: 13949

wrap your sql into a sub query so you can filter on the aliases

SELECT * FROM (
SELECT A1.LRN, A1.StartDate, A1.Destination, 
(
    SELECT TOP 1 A2.StartDate
    FROM testEnrolment As A2
    WHERE A2.LRN = A1.LRN AND A2.StartDate > A1.StartDate
    ORDER BY A2.StartDate
) As NextStartDate, 
(
    SELECT TOP 1 B2.Destination
    FROM testEnrolment As B2
    WHERE B2.LRN = A1.LRN AND B2.StartDate > A1.StartDate
    ORDER BY B2.StartDate
) As NextDestination
FROM testEnrolment As A1
) AS s
WHERE NextDestination <> 'Education'

Upvotes: 2

Related Questions