I had just asked a question about aliases and learned that a subquery in a FROM clause must be aliased by putting "AS [alias name]". Now I am still kind of running into the same problem.
I am trying to natural join 4 relations product, laptop, pc, and printer.
I can do
SELECT * FROM product NATURAL JOIN pc; very fine and easy. But I am trying to do
SELECT * FROM product NATURAL JOIN pc, laptop, printer;
which I knew probably wouldn't work when I tried.
So I am currently on
SELECT * FROM ( SELECT * FROM ( SELECT * FROM product NATURAL JOIN pc AS prod_pc ) AS prod_pc_lap NATURAL JOIN laptop ) NATURAL JOIN printer;
But I keep getting a 1248 error.
Note each relations primary key is "model".
UNIONs since printers don't relate to pcs - Michael Berkowski 2012-04-03 20:41
Why not something like this:
SELECT p.model, COALESCE(pr.price, l.price, pc.price, 0) AS price FROM product p LEFT OUTER JOIN printer pr ON pr.model = p.model LEFT OUTER JOIN laptop l ON l.model = p.model LEFT OUTER JOIN pc ON pc.model = p.model
COALESCE() function will evaluate each argument in order and return the first non-null value it sees.
, you have in the
FROM clause are (more or less), equivalent to
CROSS JOIN. So, your query is equivalent to:
SELECT * FROM product NATURAL JOIN pc CROSS JOIN laptop CROSS JOIN printer ;
If you want
NATURAL JOIN, you can have:
SELECT * FROM product NATURAL JOIN pc NATURAL JOIN laptop NATURAL JOIN printer;
The above will probably return 0 rows (unless you have a product that is a pc, laptop and printer at the same time). You can try replacing
NATURAL JOIN with
NATURAL LEFT JOIN.
For your case, I guess an inner join would work too. Anyway, with any sort of join, you can use
using(key) instead of
on tbl1.key = tbl2.key if
key is same.
So your query would look like
SELECT * FROM product INNER JOIN pc USING (model) INNER JOIN laptop USING (model) INNER JOIN printer USING (model);