View Single Post
  #3 (permalink)  
Old August 9th, 2005, 06:22 PM
mat41 mat41 is offline
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41

Thankyou for your time. No problems with distinct. The query returns the same result set with or without it. I should remove it, since I use an auto incrimenting integer, it is not possible for there to be duplicates.

;;;what is the datatype for your inactive fields
(SQLServer)bit - 0 = false and 1 = true

The problem did lye in that part of the query, when I removed:

AND (originator.orInactive <> 1) AND (shipClass.scInactive <> 1)
AND (PMPFoulingType.Inactive <> 1) AND (PMPFoulingArea.Inactive <> 1)

The result set came back as it should. For those who have a similar problem the solution required a chnage to the last part of the query:
NOTE: Changing the joins to LEFT OUTER was not the solution. Changing those last AND's to OR's fixed it. I still dont completely understand why.

SELECT originator.orName , COUNT(distinct PMPReports.Id) AS incidentNumbers
FROM (((((PMPReports
LEFT OUTER JOIN originator ON PMPReports.orId = originator.orId)
LEFT OUTER JOIN shipClass ON originator.orScId = shipClass.scId)
LEFT OUTER JOIN PMPFoulings ON PMPFoulings.reportID = PMPReports.ID)
LEFT OUTER JOIN PMPFoulingType ON PMPFoulingType.ID = PMPFoulings.typeId)
LEFT OUTER JOIN PMPFoulingArea ON PMPFoulingArea.ID = PMPFoulings.areaId)
WHERE (PMPReports.signalDateTime > '7/1/2004 12:00:00 AM'
AND PMPReports.signalDateTime < '7/1/2005 12:00:00 AM')
AND ((originator.orInactive <> 1) OR (shipClass.scInactive <> 1)
OR (PMPFoulingType.Inactive <> 1) OR (PMPFoulingArea.Inactive <> 1))
GROUP BY originator.orName

Wind is your friend