MySQL error 1248, Creating proper query. x3 Natural Joins

Go To StackoverFlow.com

1

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

2012-04-03 20:23
by NoName
I deleted my answer because I'm not understanding what output you expect. I'm wondering if what you actually need here is a stack of UNIONs since printers don't relate to pcs - Michael Berkowski 2012-04-03 20:41
I guess what I am trying to do is 'SELECT model, price FROM (SELECT * FROM product LEFT JOIN printer, laptop, pc ON product.model = printer.model = laptop.model = pc.model)' I know this is not proper syntax but I think you could understand what I mean by it. Product(maker, model, type) Laptop(Model, price, speed, ram, hd, screen) PC(model, price, speed, ram, hd) Printer(model,price, color, price - NoName 2012-04-03 20:42


0

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.

2012-04-03 21:09
by Crontab


0

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.

2012-04-03 21:16
by ypercubeᵀᴹ


0

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);
2012-04-04 06:30
by Unos