Sub Query Issue

[ooch1]

Hello,

I have created a sub query using the code below, but instead of recognising only the MPRs that are not null in GMS table when the GMS and IEU tables are joined, it just groups everything in the GMS table.

There are 45k records in the GMS table but only 34k that are also in the IEU table, it is the 34k that i want grouping by [companycode]?

SELECT GMS.CompanyCode, Count(GMS.MeterPointRef) AS Meters
FROM [A01-NBS_GAS_ROOTDATA_20040923] AS GMS

WHERE (Exists (SELECT *
               FROM [B_04 Oct IEU Portfolio] AS IEU LEFT JOIN [A01-NBS_GAS_ROOTDATA_20040923] AS GMS1 ON  IEU.[Mpo Reference (Result Object)] = GMS1.MeterPointRef 
               WHERE  IEU.[Mpo Reference (ResultObject)]       is not null))
GROUP BY GMS.CompanyCode;

OOch

 

[PHV]

Something like this ?
SELECT GMS.CompanyCode, Count(GMS.MeterPointRef) AS Meters
FROM [A01-NBS_GAS_ROOTDATA_20040923] AS GMS
INNER JOIN [B_04 Oct IEU Portfolio] AS IEU
ON GMS.MeterPointRef = IEU.[Mpo Reference (Result Object)]
GROUP BY GMS.CompanyCode;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244