Subquery Help

[Me!]

I have the following query (see below). My primary field is TECH_Id (alias
CAPId) - and I want to return just one row per CAPId value. However, I have
found that on occasions I am being returned 2 or more rows per CAPId. This
is because the query is returning multiple values for some of the other
fields (as returned by the subqueries). The source table does actually
contain mutliple values for these other fields - and there is another field
called TECH_Effective which is a date field which dates each value. What I
want to return is the required field for the *latest* date in TECH_Effective
(for each subquery).

Can anyone advise how I can tweak my subqueries to select each field for the
maximum TECH_Effective date?

Any help greattly appreciated......P.S This query is in an Access project,
hence the T-SQL syntax.

SELECT DISTINCT
Subqry_Euro.TECH_Id AS CAPId,
Subqry_Euro.TECH_Value_String AS EuroStandard,
Subqry_Wheelbase.TECH_Value_Float AS Wheelbase,
Subqry_GrossVehicleWeight.TECH_Value_Float AS
GrossVehicleWeight, Subqry_Payload.TECH_Value_Float AS Payload,
Subqry_Torque.TECH_Value_Float AS Torque,
Subqry_BHP.TECH_Value_Float AS BHP, Subqry_CC.TECH_Value_Float AS CC,
Subqry_Length.TECH_Value_String AS Length,
Subqry_LoadSpaceVolume.TECH_Value_Float AS LoadSpaceVolume
FROM dbo.LIGHTS_NVDTechnical Subqry_Euro LEFT OUTER JOIN
(SELECT TECH_Id, TECH_Value_Float
FROM dbo.LIGHTS_NVDTechnical
WHERE (TECH_TechCode = 15))
Subqry_Wheelbase ON Subqry_Wheelbase.TECH_Id = Subqry_Euro.TECH_Id LEFT
OUTER JOIN
(SELECT TECH_Id, TECH_Value_Float
FROM dbo.LIGHTS_NVDTechnical
WHERE (TECH_TechCode = 14))
Subqry_GrossVehicleWeight ON Subqry_GrossVehicleWeight.TECH_Id =
Subqry_Euro.TECH_Id LEFT OUTER JOIN
(SELECT TECH_Id, TECH_Value_Float
FROM dbo.LIGHTS_NVDTechnical
WHERE (TECH_TechCode = 32)) Subqry_Payload
ON Subqry_Payload.TECH_Id = Subqry_Euro.TECH_Id LEFT OUTER JOIN
(SELECT TECH_Id, TECH_Value_Float
FROM dbo.LIGHTS_NVDTechnical
WHERE (TECH_TechCode = 20)) Subqry_CC ON
Subqry_CC.TECH_Id = Subqry_Euro.TECH_Id LEFT OUTER JOIN
(SELECT TECH_Id, TECH_Value_Float
FROM dbo.LIGHTS_NVDTechnical
WHERE (TECH_TechCode = 21)) Subqry_BHP ON
Subqry_BHP.TECH_Id = Subqry_Euro.TECH_Id LEFT OUTER JOIN
(SELECT TECH_Id, TECH_Value_Float
FROM dbo.LIGHTS_NVDTechnical
WHERE (TECH_TechCode = 51)) Subqry_Torque
ON Subqry_Torque.TECH_Id = Subqry_Euro.TECH_Id LEFT OUTER JOIN
(SELECT TECH_Id, TECH_Value_String
FROM dbo.LIGHTS_NVDTechnical
WHERE (TECH_TechCode = 4)) Subqry_Length ON
Subqry_Length.TECH_Id = Subqry_Euro.TECH_Id LEFT OUTER JOIN
(SELECT TECH_Id, TECH_Value_Float
FROM dbo.LIGHTS_NVDTechnical
WHERE (TECH_TechCode = 39))
Subqry_LoadSpaceVolume ON Subqry_LoadSpaceVolume.TECH_Id =
Subqry_Euro.TECH_Id
WHERE (Subqry_Euro.TECH_TechCode = 68)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the IN () predicate and the CASE statement to make your query less
complex; and, I pretty sure, you only need an INNER JOIN. E.g.:

SELECT DISTINCT

E.TECH_Id AS CAPId,

E.TECH_Value_String AS EuroStandard,

CASE WHEN TC.TECH_TechCode = 15
THEN TC.TECH_Value_Float
END As Wheelbase,

CASE WHEN TC.TECH_TechCode = 14
THEN TC.TECH_Value_Float
END As GrossVehicleWeight,

CASE WHEN TC.TECH_TechCode = 32
THEN TC.TECH_Value_Float
END As Payload,

CASE WHEN TC.TECH_TechCode = 51
THEN TC.TECH_Value_Float
END As Torque,

CASE WHEN TC.TECH_TechCode = 21
THEN TC.TECH_Value_Float
END As BHP,

CASE WHEN TC.TECH_TechCode = 20
THEN TC.TECH_Value_Float
END As CC,

CASE WHEN TC.TECH_TechCode = 4
THEN TC.TECH_Value_Float
END As Length,

CASE WHEN TC.TECH_TechCode = 39
THEN TC.TECH_Value_Float
END As LoadSpaceVolume

FROM dbo.LIGHTS_NVDTechnical As E INNER JOIN dbo.LIGHTS_NVDTechnical As
TC ON E.Tech_Id = TC.Tech_ID

WHERE TC.TECH_TechCode IN (15, 14, 32, 20, 21, 51, 4, 39)
AND E.TECH_TechCode = 68

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR80gAYechKqOuFEgEQIyhgCgqrTmLFHR7lzA2bO2FzcIgkg98NwAn0uJ
yM06WTsa3vTjJIjT4B57WQ5p
=BdgK
-----END PGP SIGNATURE-----