Multiple table joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Multiple table joins

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-08-16 : 12:12:04

Hello everyone, I have the following situation:

tblAccidentEvent table relates to theEmp by empID
tblAccidentEvent table relates to tblAccidentInfo by trackingNumber
tblAccidentEvent table relates to ImagesFiles by trackingNumber
tblAccidentEvent table relates to countyDriver by accidentEventID
tblAccidentEvent table relates to tblVehicle by accidentEventID
tblAccidentEvent table relates to tblCollision by CollisionID
tblAccidentEvent table relates to tblDamage by damageID
tblAccidentEvent table relates to tblfactors by factorsID
tblAccidentEvent table relates to tblLocation by locationID
tblAccidentEvent table relates to tblweather by weatherID

The structure is like this:
tblAccidentEvent is the main table with
accidentEventID as the primary key identity not null
Empid (from theEmp table)
collisionID (from tblCollision table)
trackingNumber (from tblaccidentInfo table)
weatherID (from tblWeather table)

ImageFiles has:
ID pk not null,
trackingNumber(from tblAccidentInfo table)

tblVehicle has:
vehicleId pk identity not null
accidentEventID (from tblAccidentEvent table)
damageID (from tbldamage table)

countyDriver has:
driverID pk identity not null
accidentEventID (from tblaccidentEvent table)
driverCondition (from tblDriverCondition table)
contributingFactors (from tblfactors table)

The following are lookup tables:
tblDriverCondition,
tblLocation,
tblWeather,
tblDamage,
tblAccidentInfo,
tblFactors

I have the following query:

SELECT * FROM tblaccidentevent,
theEmp,
tblAccidentInfo,
tblDriverCondition,
Countydriver,
tblvehicle,
tblcollision,
tbldamage,
tblfactors,
tbllocation,
tblweather,
ImageFiles
where tblAccidentInfo.trackingNumber = tblaccidentEvent.trackingNumber
and tblAccidentInfo.trackingNumber = ImageFiles.trackingNumber
and tblDriverCondition.ConditionID = CountyDriver.DriverCondition
and tblAccidentEvent.accidenteventid=Countydriver.accidenteventid
and tblaccidentevent.accidenteventid = tblvehicle.accidenteventid
and tblaccidentevent.collisionid = tblcollision.collisionid
and tblvehicle.damageid = tbldamage.damageid
and tblfactors.factorsid = CountyDriver.contributingFactors
and tblaccidentevent.LocationOfImpact = tbllocation.locationid
and tblaccidentevent.weathercondition = tblweather.weatherid
and tblAccidentevent.empid = theEmp.empid
and tblAccidentevent.trackingNumber = @trackingNumber

When I run this query, some values are blank even though there are records for them on the db like
factorsDescription from tblfactors and filename from ImageFiles table.
What am I doing wrong with the query above?

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-08-16 : 18:17:32

sorry it should be more like this:

tblAccidentEvent table relates to theEmp by empID
tblAccidentEvent table relates to tblAccidentInfo by trackingNumber
tblAccidentEvent table relates to countyDriver by accidentEventID
tblAccidentEvent table relates to tblVehicle by accidentEventID
tblAccidentEvent table relates to tblCollision by CollisionID
tblAccidentEvent table relates to tblLocation by locationID
tblAccidentEvent table relates to tblweather by weatherID

CountyDriver table relates to tblDamage by damageID
CountyDriver table relates to tblfactors by factorsID

tblAccidentInfo table relates to ImagesFiles by trackingNumber

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-17 : 03:07:39

Firstly, dont use this join syntax, it intermingles the filter and join conditions in the WHERE clause. Use the "...INNER/OUTER JOIN...ON..." syntax:

SELECT * FROM
tblaccidentevent INNER JOIN theEmp
ON tblAccidentevent.empid = theEmp.empid
INNER JOIN tblAccidentInfo
ON tblAccidentInfo.trackingNumber = tblaccidentEvent.trackingNumber
INNER JOIN tblDriverCondition
ON tblAccidentEvent.accidenteventid=Countydriver.accidenteventid
INNER JOIN Countydriver
ON tblDriverCondition.ConditionID = CountyDriver.DriverCondition
INNER JOIN tblvehicle
ON tblaccidentevent.accidenteventid = tblvehicle.accidenteventid
INNER JOIN tblcollision
ON tblaccidentevent.collisionid = tblcollision.collisionid
INNER JOIN tbldamage
ON tblvehicle.damageid = tbldamage.damageid
INNER JOIN tblfactors
ON tblfactors.factorsid = CountyDriver.contributingFactors
INNER JOIN tbllocation
ON tblaccidentevent.LocationOfImpact = tbllocation.locationid
INNER JOIN tblweather
ON tblaccidentevent.weathercondition = tblweather.weatherid
INNER JOIN ImageFiles
AND tblAccidentInfo.trackingNumber = ImageFiles.trackingNumber
WHERE tblAccidentevent.trackingNumber = @trackingNumber

Now run this and see where it takes you...

Owais

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-08-17 : 18:10:57

Perfect!
I just made a slight change and it worked like a charm.
Thank you!

SELECT * FROM
tblaccidentevent INNER JOIN theEmp
ON tblAccidentevent.empid = theEmp.empid
INNER JOIN tblAccidentInfo
ON tblAccidentInfo.trackingNumber = tblaccidentEvent.trackingNumber
INNER JOIN Countydriver
ON Countydriver.accidenteventid = tblaccidentevent.accidenteventid
INNER JOIN tblDriverCondition
ON Countydriver.DriverCondition=tblDriverCondition.conditionID
INNER JOIN tblvehicle
ON tblaccidentevent.accidenteventid = tblvehicle.accidenteventid
INNER JOIN tblcollision
ON tblaccidentevent.collisionid = tblcollision.collisionid
INNER JOIN tbldamage
ON tblvehicle.damageid = tbldamage.damageid
INNER JOIN tblfactors
ON tblfactors.factorsid = CountyDriver.contributingFactors
INNER JOIN tbllocation
ON tblaccidentevent.LocationOfImpact = tbllocation.locationid
INNER JOIN tblweather
ON tblaccidentevent.weathercondition = tblweather.weatherid
INNER JOIN ImageFiles
ON tblAccidentInfo.trackingNumber = ImageFiles.trackingNumber
WHERE tblAccidentevent.trackingNumber =134

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-08-18 : 05:49:41

Do you really need to do select * ?

Can't you cut it down to reduce traffic, by only selecting the columns you need ? If not, at least remove all the "join columns" from the result set to reduce the amount of redundant data you are returning.
EG :
tblAccidentInfo.trackingNumber = ImageFiles.trackingNumber : so you only need one of these columns, not both.

The same is true for each of your INNER join critieria - you have the same values. A rough eyeball seems to indicate you have 12 redundant columns in youur resultset.

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources