OUTER JOIN 2 (or more) tables at once

zeroreality
zeroreality used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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
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

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial