MySQL Server
--
Questions
--
Followers
Top Experts
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?
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
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
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
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
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'
/*------------------------
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
MySQL Server
--
Questions
--
Followers
Top Experts
MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.