#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Location
    Rotterdam
    Posts
    2
    Rep Power
    0

    INNER JOIN subquery SELECT


    Hi guys! I am trying to select some data from a table in an Access 2010 database. The thing was: I wanted to display the latest "Configuration" release of each System (adapter). Well, using the SQL code below it seems to work perfectly, but I am having trouble filtering out the "QaEnv", too.

    This is what is displayed before the query:

    ID | QaEnv | System | Type | Configuration | ActiveDate
    1 | Delta (UAT2) EAI-V3 | MOMS Adapter | Adapter | 1.0 | 30-06-2011
    2 | Delta (UAT2) EAI-V3 | GSG Adapter | Adapter | 1.1 | 30-06-2011
    3 | Yankee (UAT1) ACC-V01 | MOMS Adapter | Adapter | 2.4 | 30-06-2011
    4 | Delta (UAT2) EAI-V3 | GSG Adapter | Adapter | 1.0 | 20-06-2011
    5 | Delta (UAT2) EAI-V3 | KIWI2 Adapter | Adapter | 1.7 | 30-06-2011
    6 | Delta (UAT2) EAI-V3 | KIWI2 Adapter | Adapter | 1.5 | 20-06-2011

    This is what is displayed after the query:

    ID | QaEnv | System | Type | Configuration | ActiveDate
    1 | Delta (UAT2) EAI-V3 | MOMS Adapter | Adapter | 1.0 | 30-06-2011
    2 | Delta (UAT2) EAI-V3 | GSG Adapter | Adapter | 1.1 | 30-06-2011
    3 | Yankee (UAT1) ACC-V01 | MOMS Adapter | Adapter | 2.4 | 30-06-2011
    5 | Delta (UAT2) EAI-V3 | KIWI2 Adapter | Adapter | 1.7 | 30-06-2011

    But I want to display this:

    ID | QaEnv | System | Type | Configuration | ActiveDate
    1 | Delta (UAT2) EAI-V3 | MOMS Adapter | Adapter | 1.0 | 30-06-2011
    2 | Delta (UAT2) EAI-V3 | GSG Adapter | Adapter | 1.1 | 30-06-2011
    4 | Delta (UAT2) EAI-V3 | KIWI2 Adapter | Adapter | 1.7 | 30-06-2011

    Code:
    SELECT t.*
    FROM Deployments t
    INNER JOIN
    (
    SELECT System, Max(ActiveDate) as ActDate
    FROM Deployments
    WHERE QaEnv = "Delta (UAT2) EAI-V3" AND Type = "Adapter" 
    AND (([ActiveDate] Between #6/20/2011# And #6/30/2011#)) 
    GROUP BY System
    ) 
    x ON t.system = x.system AND t.ActiveDate = x.ActDate;
    Can any of you help me out?

    Thanks in advance,
    Dennis
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Location
    Rotterdam
    Posts
    2
    Rep Power
    0
    A friend found the answer :-) it prints the results I am looking for.

    Code:
    SELECT t.QaEnv, t.System, t.Type, MAX(t.Configuration) as Configuration, MAX(t.ActiveDate) as ActiveDate
    FROM Deployments t
    WHERE t.QaEnv = "Delta (UAT2) EAI-V3" 
    AND t.Type = "Adapter" 
    AND (([t.ActiveDate] Between #6/20/2011# AND #6/30/2011#))
    GROUP BY t.QaEnv, t.System, t.Type

    Comments on this post

    • MrFujin agrees : +1 for posting the answer

IMN logo majestic logo threadwatch logo seochat tools logo