zeroreality
asked on
OUTER JOIN 2 (or more) tables at once
I need to know if it is possible to OUTER JOIN multiple tables at the same time.
this would be like OUTER JOINing 2 queries together
this code works (but is a pain to update and takes forever to run):
SELECT
transactions.ID,
transactions.ProdID,
transactions.RatePlan,
transactions.ServArea,
products.ID as prodID,
products.Name as prodName,
rateplans.ID as rateID,
rateplans.Name as rateName,
servarea.ID as servID,
servarea.Name as servName
FROM carts, transactions, products
/*------------------------ ---------- ---------- --*/
LEFT OUTER JOIN rateplans
ON rateplans.ID = transactions.RatePlan
LEFT OUTER JOIN servarea
ON servarea.ID = transactions.ServArea
/*------------------------ ---------- ---------- --*/
WHERE products.ID = transactions.ProdID
AND transactions.CartID = carts.ID
AND carts.ID = 'the cart id im tracking'
however the system is going to be MORE complicated in the future and i need to be able to do something like the following (so i dont pull my hair out):
SELECT
transactions.ID,
transactions.ProdID,
transactions.RatePlan,
transactions.ServArea,
products.ID as prodID,
products.Name as prodName,
rateplans.ID as rateID,
rateplans.Name as rateName,
servarea.ID as servID,
servarea.Name as servName
FROM carts, transactions, products
/*------------------------ ---------- ---------- --*/
LEFT OUTER JOIN rateplans, servarea
ON rateplans.ID = transactions.RatePlan
AND servarea.ID = transactions.ServArea
/*------------------------ ---------- ---------- --*/
WHERE products.ID = transactions.ProdID
AND transactions.CartID = carts.ID
AND carts.ID = 'the cart id im tracking'
but i get errors with syntax...
is this possible in MySQL or do I have to do it the long way?
this would be like OUTER JOINing 2 queries together
this code works (but is a pain to update and takes forever to run):
SELECT
transactions.ID,
transactions.ProdID,
transactions.RatePlan,
transactions.ServArea,
products.ID as prodID,
products.Name as prodName,
rateplans.ID as rateID,
rateplans.Name as rateName,
servarea.ID as servID,
servarea.Name as servName
FROM carts, transactions, products
/*------------------------
LEFT OUTER JOIN rateplans
ON rateplans.ID = transactions.RatePlan
LEFT OUTER JOIN servarea
ON servarea.ID = transactions.ServArea
/*------------------------
WHERE products.ID = transactions.ProdID
AND transactions.CartID = carts.ID
AND carts.ID = 'the cart id im tracking'
however the system is going to be MORE complicated in the future and i need to be able to do something like the following (so i dont pull my hair out):
SELECT
transactions.ID,
transactions.ProdID,
transactions.RatePlan,
transactions.ServArea,
products.ID as prodID,
products.Name as prodName,
rateplans.ID as rateID,
rateplans.Name as rateName,
servarea.ID as servID,
servarea.Name as servName
FROM carts, transactions, products
/*------------------------
LEFT OUTER JOIN rateplans, servarea
ON rateplans.ID = transactions.RatePlan
AND servarea.ID = transactions.ServArea
/*------------------------
WHERE products.ID = transactions.ProdID
AND transactions.CartID = carts.ID
AND carts.ID = 'the cart id im tracking'
but i get errors with syntax...
is this possible in MySQL or do I have to do it the long way?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
svitch tablecards with table transactions
FROM transactions
/*------------------------ ---------- ---------- --*/
LEFT OUTER JOIN rateplans
ON rateplans.ID = transactions.RatePlan -- could not be tested with transactions coming later
LEFT OUTER JOIN servarea
ON servarea.ID = transactions.ServArea
INNER JOIN products
ON products.ID = transactions.ProdID
INNER JOIN carts
ON transactions.CartID = carts.ID AND
carts.ID = 'the cart id im tracking'
/*------------------------ ---------- ---------- --*/
FROM transactions
/*------------------------
LEFT OUTER JOIN rateplans
ON rateplans.ID = transactions.RatePlan -- could not be tested with transactions coming later
LEFT OUTER JOIN servarea
ON servarea.ID = transactions.ServArea
INNER JOIN products
ON products.ID = transactions.ProdID
INNER JOIN carts
ON transactions.CartID = carts.ID AND
carts.ID = 'the cart id im tracking'
/*------------------------
a join b ON a.id=b.id -- create a compound table
join c ON a.id=c.id -- joins that compound table to c. etc...
It does not matter which kind of join it is.
mySQL names are not case sensitive so I would reccomend you awoid the alias prodID when already there is a field called ProdID. (not sure it is a syntac error though).
What does you mysql error message say? I see no obvious errors.
regards JakobA