I have a join that is getting to many results. I have tryed many different types of joins without success (just when I thought my head was above water)I have three tables 1.osirReports (main table listing OHS incidents reports-one to many relationship with incidents) 2.incidents (to enable each incident report to have multiple incident types) 3.incidentTypes (master list of incident types - fall, break etc - one to many with incidents)

EG: A person falls on a hot plate in a kitchen suffering burns: 1.osirReports has an incident report entry. 2.Incidents table has two entries, one for a fall and one for a burn (note:fall and burn are in the incidentType table these are represented by integers in the incident table - one to many relationship) When I query the main table (osirReports) to list OHS incident reports on the index page, I am getting duplicate listings for each incident report that has multiple incidet types. Here is my query:

SELECT osirReport.osirId, osirReport.osirSignalDateTime, osirReport.osirShipNum FROM osirReport
INNER JOIN incidents ON incidents.incOsirId = osirReport.osirId INNER JOIN incidentType ON incidentType.incidentID = incidents.incIncidentId
WHERE (osirReport.osirStatus < 3) AND (osirReport.osirOrId = 77)
ORDER BY osirReport.osirSignalDateTime DESC

This query returns correct results if there are no more than one incident type for each incident report. select distinct fixes the problem, however this is not an option due to other circumstances.

Wind is your friend
Matt

__________________
Wind is your friend
Matt

You could try to group by the select fields:

Why do you think you are getting "too many results"? What is it you are looking for?

When you JOIN your 'osirReports' table to your 'incidents' table on the osirID, you will get one row for each combination of rows in the two tables where the JOIN condition is true, that is where 'incidents.incOsirId = osirReport.osirIDd'. Since the 'incidents' table can contain more than one row for a given 'osirID', the JOIN condition will be true for more than one row,
and the result of the JOIN will thus have as many rows as there are rows in the two tables combined. This is precisely what an INNER JOIN is supposed to do.

You need to state your requirement more precisely, i.e. what your definition of 'correct' is...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Gert - Grouping by solved my problem, thank you kindly!
Jeff - Yes the join was behaving as it should. My definition of correct was slightlt incorrect
Thankyou both for your time n effort

Wind is your friend
Matt

Hello sql people

I have applyed your group by clause to my sql with success in all areas that were showing me more than I wanted.
There is one more statement that I troubling me, the reason is exaclty the same as my initial post.
The objective if this join is to pull one record: How may incident has the originatorId 77 had in the given period (based on user selections).
The result I am getting for this is 11, it should be showing 5 (5)+.
However for this incident there are two injury types (2)+
                                    two incident types (2)+
                                and two bodily locations hurt (2)
this is the reson it is pulling eleven instead of five =11

What's the chances of you working your majic on this statement like you did the last one?
The where part of this statement is made up from user selections, I have cut it down to on to limit this post.
This is the reason there are so many joins, BTW it works like a treat if there are not multiple incidents, injurys and bodily locations hurt.

SELECT originator.orName, COUNT(*) AS incidentNumbers FROM osirReport
INNER JOIN originator ON osirReport.osirOrId = originator.orId
INNER JOIN shipClass ON shipClass.scId = originator.orScId
INNER JOIN incidents ON incidents.incOsirId = osirReport.osirId
INNER JOIN incidentType ON incidentType.incidentId = incidents.incIncidentId
INNER JOIN injurys ON injurys.injOsirId = osirReport.osirId
INNER JOIN injuryType ON injuryType.injuryId = injurys.injInjuryId
INNER JOIN bLocations ON bLocations.blOsirId = osirReport.osirId
INNER JOIN bodyLocations ON bodyLocations.bodyId = blocations.blBodyId
WHERE (osirReport.osirSignalDateTime > '01/07/2003' AND osirReport.osirSignalDateTime < '1/07/2004')
AND (originator.orId IN (77))
GROUP BY originator.orName

As you can see I have included your group by clause at the end.
This fixed every other statement I was having trouble with.

I hope I have been clear in my post
Thankyou in advance
Matt (yahoo messenger = mat41)

Wind is your friend
Matt

SQL People

Problem solved, sorry to take your time again (bad attention to detail for me this week)
I looked at it with my eyes open and found I was asking for COUNT(*) - no wonder i was getting all records.


Wind is your friend
Matt