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".

• mysql

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

The COALESCE() function will evaluate each argument in order and return the first non-null value it sees.

The commas , 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);