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.
| Author |
Topic |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2003-09-11 : 14:13:54
|
| I got help with this query from here.Hope I can bother you one more time.UNION operator, as I understand it, is supposed combine data from more than one table and give you one result.The situation that I have here is one where information is stored at many different places.Of interest to us is to retrieve information stored at two different tables - CompanyDriver table and OtherDriver table.CompanyDriver table is supposed to house all data related to company drivers who get involved in accidents while otherDriver table houses information about other non-company drivers.The reason is because company drivers are assumed to be at fault in case of an accident 90% of the time.So in those rare 10% when a company driver is not at fault his/her information is stored in OtherDriver table.I have therefore been trying to do a query that retrieves and displays info related to ONLY company drivers.To the best of my knowledge, the way to go about it is to use the UNION operator to get infor from both tables based on a certain criteria.In this case, there is a field called CountyFlag in both tables.What my query is saying is that if CountyFlag = 1 retrieve data from each table where there is one common ID.When I run each individual query (UNION query and target list), they work but when I merge them, they don't.Can someone please take a look at this code and tell me what I am doing wrong?Sorry about the long speech. SELECT * FROM tblaccidentevent INNER JOIN theEmp ON tblAccidentevent.empid = theEmp.empid INNER JOIN CompanyDriver ON CompanyDriver.accidenteventid = tblaccidentevent.accidenteventid INNER JOIN tblDriverCondition ON CompanyDriver.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 CompanyDriver.contributingFactors = tblfactors.factorsid INNER JOIN tbllocation ON tblaccidentevent.LocationOfImpact = tbllocation.locationid INNER JOIN tblweather ON tblaccidentevent.weathercondition = tblweather.weatherid WHERE countyflag = 1 AND CompanyDriver.trackingNumber = @paramUNION SELECT * FROM tblaccidentevent INNER JOIN theEmp ON tblAccidentevent.empid = theEmp.empid INNER JOIN OtherDriver ON OtherDriver.accidenteventid = tblaccidentevent.accidenteventid INNER JOIN tblDriverCondition ON OtherDriver.DriverCondition=tblDriverCondition.conditionID INNER JOIN othervehicle ON tblaccidentevent.accidenteventid = othervehicle.accidenteventid INNER JOIN tblcollision ON tblaccidentevent.collisionid = tblcollision.collisionid INNER JOIN tbldamage ON othervehicle.damageid = tbldamage.damageid INNER JOIN tblfactors ON OtherDriver.contributingFactors = tblfactors.factorsid INNER JOIN tbllocation ON tblaccidentevent.LocationOfImpact = tbllocation.locationid INNER JOIN tblweather ON tblaccidentevent.weathercondition = tblweather.weatherid WHERE countyflag = 1 AND CompanyDriver.trackingNumber = @param |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2003-09-11 : 14:39:09
|
| Is this a typo in the second-half of the UNION?"AND CompanyDriver.trackingNumber = @param"because the CompanyDriver table is not used in it. |
 |
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2003-09-11 : 15:00:50
|
| sorry that is a typo.It should be OtherDriver.trackingNumber and that is the way I have it.The typo is just here. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-11 : 15:15:21
|
| Well my guess is you've got a different number of columns. Don't do SELECT *Also if the columns have different data types in the same ordinal position you'll be hosded...Don't do SELECT *...except for analysisBrett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2003-09-11 : 16:16:48
|
| that was precisely the problem - i was doing select *.as soon as i came back to my senses, it worked.thank you, now i have got to figure out how to make it work in asp. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-11 : 16:41:06
|
| Great!and btw my guess is all the id columns are identity...you can tell me....Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
|
|
|
Making Union Query Work