Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    19

    Unanswered: Merge 2 Join Queries

    Hi all,

    I have 2 seperate queries that need to be merged into one so that I can use
    this query from the Delphi environment.

    My frist query is query10:
    Code:
    SELECT Max(gpscoordinaten.lVolgNr) AS MaxGPS, gpscoordinaten.lRitId
    FROM gpscoordinaten
    GROUP BY gpscoordinaten.lRitId
    HAVING (((gpscoordinaten.lRitId) In 
    
    (SELECT ritregels.lRitId
    FROM ritregels
    WHERE (((ritregels.lRitId) In (SELECT Max(rit.lID) AS MaxVanlID
    FROM rit
    WHERE rit.sserienummerstrooier is not null
    GROUP BY rit.sserienummerstrooier)) AND ((ritregels.lRegelnummer) Is Not Null)
    )
    GROUP BY ritregels.lRitId)
    
    ));
    My second query is query11:
    Code:
    SELECT GPSCoordinaten.dLongitude, GPSCoordinaten.dLatitude, GPSCoordinaten.
    lVolgNr
    FROM GPSCoordinaten INNER JOIN Query10 ON (GPSCoordinaten.lRitId = Query10.
    lRitId) AND (GPSCoordinaten.lVolgNr = Query10.MaxGPS)
    WHERE (((GPSCoordinaten.lVolgNr) In (SELECT Max(gpscoordinaten.lVolgNr) AS
    MaxGPS
    FROM gpscoordinaten
    GROUP BY gpscoordinaten.lRitId
    HAVING (((gpscoordinaten.lRitId) In (SELECT ritregels.lRitId
    FROM ritregels
    WHERE (((ritregels.lRitId) In (SELECT Max(rit.lID) AS MaxVanlID
    FROM rit
    WHERE rit.sserienummerstrooier is not null
    GROUP BY rit.sserienummerstrooier)) AND ((ritregels.lRegelnummer) Is Not Null)
    )
    GROUP BY ritregels.lRitId))))))
    ORDER BY GPSCoordinaten.lVolgNr;
    I need it as 1 query that means that the query10 should not exist anymore in
    query11. I've tried several possiblelities but all ended up in wrong SQL.

    Is there anybody who can give at least a hit on this one?

    Regards and many many thankx,

    Jânio

  2. #2
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    Try this:

    SELECT GPSCoordinaten.dLongitude, GPSCoordinaten.dLatitude, GPSCoordinaten.
    lVolgNr
    FROM GPSCoordinaten INNER JOIN (SELECT Max(gpscoordinaten.lVolgNr) AS MaxGPS, gpscoordinaten.lRitId
    FROM gpscoordinaten
    GROUP BY gpscoordinaten.lRitId
    HAVING (((gpscoordinaten.lRitId) In

    (SELECT ritregels.lRitId
    FROM ritregels
    WHERE (((ritregels.lRitId) In (SELECT Max(rit.lID) AS MaxVanlID
    FROM rit
    WHERE rit.sserienummerstrooier is not null
    GROUP BY rit.sserienummerstrooier)) AND ((ritregels.lRegelnummer) Is Not Null)
    )
    GROUP BY ritregels.lRitId)
    ) Q10 ON (GPSCoordinaten.lRitId = Q10.
    lRitId) AND (GPSCoordinaten.lVolgNr = Q10.MaxGPS)
    WHERE (((GPSCoordinaten.lVolgNr) In (SELECT Max(gpscoordinaten.lVolgNr) AS
    MaxGPS
    FROM gpscoordinaten
    GROUP BY gpscoordinaten.lRitId
    HAVING (((gpscoordinaten.lRitId) In (SELECT ritregels.lRitId
    FROM ritregels
    WHERE (((ritregels.lRitId) In (SELECT Max(rit.lID) AS MaxVanlID
    FROM rit
    WHERE rit.sserienummerstrooier is not null
    GROUP BY rit.sserienummerstrooier)) AND ((ritregels.lRegelnummer) Is Not Null)
    )
    GROUP BY ritregels.lRitId))))))
    ORDER BY GPSCoordinaten.lVolgNr;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •