triple table join - 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

SQL Server 2000 Forums

Transact-SQL (2000)

triple table join

Author

Topic

superc0ntra
Starting Member

2 Posts

Posted - 2002-04-11 : 09:16:43

When I am trying to join three tables using the following syntax.
-------------------------------------------------
SELECT TOP 5 tvehicles.regnr,
tvehicles.model,
tvehicles.yearmodel,
sum(tevents.eventcost) as eventcost,
sum(tequipment.equipmentcost) as equipmentcost
FROM tvehicles join tevents on objectid = tevents.vehicleid
left outer join tequipment on objectid = tequipment.vehicleid
group by regnr, model, yearmodel
-------------------------------------------
the values in the sum(...) columns are doubled (some kind of cartesian product is formed), I suspect it has to do with the third table joined because if I join only tvehicles and tevents it works fine.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-11 : 09:24:19

You'd have to post your DDL before getting into specifics, but generally speaking the JOIN operator ( set intersection ) multiplies two sets together, so you're right that some sort of 'product' is being returned.

setBasedIsTheTruepath
<O>

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-04-11 : 12:41:47

Without any DDL, we can only guess at data, keys, constraints, everything that is important to answering your question. Do you work from specs like this at your job?

But you should never use a prefix like "t-" for table that describes the PHYSICAL storage instead of the LOGICAL meaning of the data. This is SQL in 2002, not Fortran or BASIC in 1960.

There is not such thing as an object identifier in SQL; we use keys. So you have a join on a totally meaningless column -- or you have a hopeless data model. I see this a lot when OO programmers write SQL for the first time; and I charge huge consulting fees to fix it when it fails.

My first guess is that you wanted to say:

CREATE TABLE Vehicles
(vehicle_id CHAR(35) NOT NULL PRIMARY KEY, -- VIN number?
reg_nr INTEGER NOT NULL, -- not a key?
model CHAR(7) NOT NULL,
year_model INTEGER NOT NULL,
...);

CREATE TABLE Events
(event_name CHAR(20) NOT NULL,
event_date DATE NOT NULL,
vehicle_id CHAR(35) NOT NULL
REFERENCES Vehicles(vehicle_id),
...
PRIMARY KEY (event_name, vehicle_id));

CREATE TABLE Equipment
(equipment_id INTEGER NOT NULL,
equipment_name CHAR(20) NOT NULL,
vehicle_id CHAR(35) NOT NULL
REFERENCES Vehicles(vehicle_id),
...
PRIMARY KEY (equipment_id, vehicle_id));

and you want a query like:

SELECT V1.reg_nr, V1.model, V1.year_model,
SUM(E1.event_cost) AS event_cost,
SUM(Q1.equipment_cost) AS equipment_cost
FROM Vehicles AS V1, Events AS E1, Equipment AS Q1
WHERE V1.vehicle_id = E1.vehicle_id
AND V1.vehicle_id = Q1.vehicle_id
GROUP BY V1.reg_nr, V1.model, V1.year_model;

or perhaps:

SELECT V1.reg_nr, V1.model, V1.year_model,
SUM(E1.event_cost) AS event_cost,
SUM(Q1.equipment_cost) AS equipment_cost
FROM (Vehicles AS V1
LEFT OUTER JOIN
Events AS E1
ON V1.vehicle_id = E1.vehicle_id)
LEFT OUTER JOIN
Equipment AS Q1
ON V1.vehicle_id = Q1.vehicle_id
GROUP BY V1.reg_nr, V1.model, V1.year_model;

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources