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?
LVL 2
zerorealityAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JakobACommented:
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
keakathleenCommented:
I do not believe that there is anyway to combine two OUTER JOIN's in the way you mentioned above. As JakobA mentioned, you can join as many tables as you want, but they each have to be followed by a join clause like in your first example

Your syntax error would be most likely be the comma between the two tables on the left outer join as MySQL is not expecting a comma or another table here.

Can you put everything in a join clause to make it less confusing?

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
/*----------------------------------------------*/
LEFT OUTER JOIN rateplans
ON rateplans.ID = transactions.RatePlan
LEFT OUTER JOIN servarea
ON servarea.ID = transactions.ServArea
INNER JOIN products
ON products.ID = transactions.ProdID
INNER JOIN transactions
ON transactions.CartID = carts.ID AND
carts.ID = 'the cart id im tracking'
/*----------------------------------------------*/

Or something like this...I am not sure exactly how your query works.

kea

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JakobACommented:
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'
/*----------------------------------------------*/
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.