p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   Please have a look and advise on my join (http://p2p.wrox.com/showthread.php?t=32239)

mat41 August 6th, 2005 11:08 PM

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

sdilucca August 9th, 2005 01:08 PM

ok... i'm going to take a crack at this...

I have used the expression "COUNT(distinct" in the past and it has given me problems. Maybe it's not giving you problems. Not sure.

I would use the BETWEEN function in your WHERE clause for the date filtering.

Question... what is the datatype for your inactive fields?

Julio Cesar

-"Life is a Database"

mat41 August 9th, 2005 06:22 PM

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
Matt


All times are GMT -4. The time now is 06:31 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.