Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 6th, 2005, 11:08 PM
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
  #2 (permalink)  
Old August 9th, 2005, 01:08 PM
Authorized User
 
Join Date: Jun 2004
Location: , , USA.
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"
  #3 (permalink)  
Old August 9th, 2005, 06:22 PM
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

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
OO Advise and Clarification rodmcleay General .NET 1 October 14th, 2004 09:51 PM
Problems with Java Mail Please advise "Chapter 9" PatrickWalsh JSP Basics 1 September 5th, 2004 06:46 PM
Oracle 8i inner join and left join problem puteri_84 Oracle 2 August 19th, 2004 07:14 AM
$ command not recognised.-Can you please advise PatrickWalsh JSP Basics 14 January 17th, 2004 09:56 AM





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