The query:
select distinct
tblAutoSystem.AutoSystemID, tblAutoSystem.AutoSystemDescription ,
count(1) as CNT
from tblTSBToVehicleXref
join tblTSBToAutoSystemXref on
tblTSBToVehicleXref.tsbID=tblTSBToAutoSystemXref.tsbID
join tblAutoSystem on
tblAutoSystem.AutoSystemID=tblTSBToAutoSystemXref.AutoSystemID
where vehicleID in ( '1001114','1001125','1001136')
group by tblAutoSystem.AutoSystemDescription,tblAutoSystem.AutoSystemID
order by tblAutoSystem.AutoSystemDescription
Returns:
71 Automatic Trans, Coolers, Torque Converter 3
The number on the end of the data is the count of the records in each group
The actual DISTINCT count of the 2 fields is 1.
The following query shows the actual data.
select VehicleID, tblTSBToVehicleXref.TSBID,
tblTSBToAutoSystemXref.AutoSystemID, AutoSystemDescription
from tblTSBToVehicleXref
join tblTSBToAutoSystemXref on
tblTSBToVehicleXref.TSBID=tblTSBToAutoSystemXref.TSBID
join tblAutoSystem on
tblAutoSystem.AutoSystemID=tblTSBToAutoSystemXref.AutoSystemID
where vehicleID in('1001114','1001125','1001136') and
tblAutoSystem.autosystemid='71'
1001114 7404 71 Automatic Trans, Coolers, Torque Converter
1001125 7404 71 Automatic Trans, Coolers, Torque Converter
1001136 7404 71 Automatic Trans, Coolers, Torque Converter
What am I doing wrong?
Do I need to a a subquery in place of the count() to get the correct number?