View Single Post
  #1 (permalink)  
Old August 6th, 2005, 11:08 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
Default Please have a look and advise on my join

I have a record set comming back that has no error but is not showing all the data it should. Here is the table set up relative to the query.

1..Main table:
PMPReports table (pollution reports):
ID (PK)
orID (FK to originator table - one to many relationship)

2..Originator table (master list of ships):
ID (PK)
orScID (foriegn key to shipclass table - one to many relationship)

3..ShipClass table (master list of ship classes):
(ID)

4..PMPFoulings table (a report may have many foulings in many areas):
ID (PK)
reportID (foriegn key to PMPReports table - one to many relationship)
typeID (foriegn key to PMPFoulingType table - one to many)
areaID (foriegn key to PMPFoulingArea table - one to many)

5..PMPFoulingType table (master list of fouling types):
(ID)

6..PMPFoulingArea table (master list of fouling areas):
(iD)

Here is the query:

SELECT originator.orName , COUNT(distinct PMPReports.Id) AS incidentNumbers
FROM (((((PMPReports
INNER JOIN originator ON PMPReports.orId = originator.orId)
LEFT OUTER JOIN shipClass ON originator.orScId = shipClass.scId)
INNER 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) AND (shipClass.scInactive <> 1)
AND (PMPFoulingType.Inactive <> 1) AND (PMPFoulingArea.Inactive <> 1)
GROUP BY originator.orName

Her is the result set:

(originators) (number of incidents)
MELVILLE 1
NAS NOWRA 1
NEWCASTLE 1
RANKIN 1
SHOALWATER 1

The problem: The result set is failing to show an incident
for any originators where the fouling type(s) and area(s) has yet to
be defined. The design concept of the system is as follows:
1..signals come in from ships
2..they get entered into the system
3..When an administrator get around to it, he identifies what
type of fouling(s) have occurred in which area(s).

I would appreciate some assistance with my join type(s) I have been
trial and erroring it for a while now, I can not get the result set to change. This query is used to produce a bar graph.

PROBLEM SUMMARY:
The graph (record set above) only shows records for originators that have records in the PMPFoulings table. You may say why do I need to join all these tables just to get the number of incidents for an originator. It is a graph wizard, they can choose from a selection of conditional criteria. Two of these options are founlingTypes and foulingAreras.

TYIA

Wind is your friend
Matt
__________________
Wind is your friend
Matt
Reply With Quote