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?
Our community of experts have been thoroughly vetted for their expertise and industry experience.
This award recognizes a member of Experts Exchange who has made outstanding contributions to the community within their first year as an expert. The Rookie of the Year is awarded to a new expert who has the highest number of quality contributions.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.