Sql brings out duplicate data

[shortstuff2212]

I have the following sql statement in the a report:-

SELECT Election.`ElectionID`, ElectionArea.`ElectionAreaName`, ElectionArea.`PollDist_Row`, ElectionArea.`EA_Code`, Election_PollingPlace.`Polling_Place`, Election_PollingPlace.`PD_Code`, Election_PollingPlace.`PS_Num`, PollingDistrict.`DistrictWard`, PollingStationRange.`ElectorateFrom`, PollingStationRange.`ElectorateTo`
FROM ((((Election AS Election INNER JOIN ElectionArea AS ElectionArea ON Election.`ElectionId` = ElectionArea.`ElectionID`) INNER JOIN PollingDistrict AS PollingDistrict ON ElectionArea.`EA_Code` = PollingDistrict.`DistrictWard`) INNER JOIN Election_PollingPlace AS Election_PollingPlace ON ElectionArea.`ElectionID` = Election_PollingPlace.`Election_ID`) INNER JOIN EM_PollingPlace AS EM_PollingPlace ON (Election_PollingPlace.`PD_Code` = EM_PollingPlace.`PD_Code`) AND (Election_PollingPlace.`Polling_Place` = EM_PollingPlace.`Polling_Place`)) INNER JOIN PollingStationRange AS PollingStationRange ON (Election_PollingPlace.`PS_Num` = PollingStationRange.`PSNumber`) AND (Election_PollingPlace.`PD_Code` = PollingStationRange.`PDCode`) AND (Election_PollingPlace.`PS_Code` = PollingStationRange.`PSCode`)
WHERE Election.`ElectionID` = 4 AND
ElectionArea.`ElectionAreaName` = 'MATLOCK ALL SAINTS'
ORDER BY
ElectionArea.`EA_Code`, Election_PollingPlace.`PD_Code`, Election_PollingPlace.`PS_Num`, PollingStationRange.`ElectorateFrom`;

This sql brings out the same record three times, one for each electoral area.

MATLOCK ALL SAINTS
DENHAM
ENDON

How do I stop this.

Any help would be appreciated.

Kath

 

[synapsevampire]

If they are identical, the simples means is to add the word DISTINCT after SELECT.

I didn't analyze the SQL closely, but it looks like you're using inner joins, which is probably correct, so it's a cartesian being raised as a result of the data itself I'd guess.

Here's a definition of a cartesian:

http://c2.com/cgi/wiki?CartesianJoins

A quick cheat is to group by the Electoral Area and display data in the group footer or header only.

-k

 

[shortstuff2212]

Thanks for the help it worked