August 16, 2003 at 10:16 am
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?
August 16, 2003 at 3:40 pm
Are there any missing entities in your join? I'm (probably wrongly) assuming that there can be several contributiong factors to an accident/driver. What is the definition of the contributingFactors column?
Cheers,
- Mark
Cheers,
- Mark
August 16, 2003 at 4:01 pm
tblfactors is the table with two fields
factorsId int
factorsDesc varchar(50)
so we have
factorsID factorsDesc
1 factorsDesc1
2 factorsdesc2
etc.
so if we have a query:
select factorsDesc
from tblfactors, CountyDriver
where tblfactors.factorsID = countyDriver.contributingFactors
and tblfactors.factorsID = 1,
I will see a value of factorsDesc1.
This value will satisfy that above query.
However, when I have multiple table joins such as above, nothing gets returned.
I hope I understood and answered your question correctly.
Thanks for your response
August 16, 2003 at 4:22 pm
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
August 16, 2003 at 4:29 pm
I was thinking maybe countyDriver.contributingFactors was a varchar comma-separated list of FactorIDs, but your response kills that theory.
The JOIN must be working, otherwise you'd get no rows returned, rather then empty fields.
Just as another test, is anything returned if you replace:
SELECT * FROM
with:
SELECT tblfactors.factorsDescription , ImagesFiles.filename FROM
Cheers,
- Mark
Cheers,
- Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply