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?

JakobA

Yes, you can make as many outer joins as you like. join is just a binary operator combining 2 tables into one.
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

ASKER CERTIFIED SOLUTION

keakathleen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JakobA

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'
/*----------------------------------------------*/