Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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"
Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:27 AM.


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