I am trying to perform a query as such to MySql

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, I am trying to find the model and price of every product. I first tried to natural join each list to product but came up with an empty set because the first natural join removed all other tuples for the other two products and then the next subsequent join removed everything.

My true question is what type of join should I use to do this?

Relations:

Product(maker, model, type)

Laptop(Model, price, speed, ram, hd, screen)

PC(model, price, speed, ram, hd)

Printer(model,price, color, price)

• mysql
• sql

Why the SELECT x from (SELECT ...)? Go right to the inner part:

SELECT model, price
FROM product
LEFT JOIN printer, laptop, pc
ON product.model = printer.model = laptop.model = pc.model

So obviously this still doesn't work because there's some syntax errors. What you need to do is multiple joins:

SELECT model, price
FROM product
LEFT JOIN printer ON product.model = printer.model
LEFT JOIN laptop ON product.model = laptop.model
LEFT JOIN pc ON product.model = pc.model

Now, this is a valid query, but I suspect in your data it won't work because you have a price in each of printer, laptop and pc (so it will be confused about which one you mean by "price"). There's probably a nicer way to do this, but you could do:

SELECT model, COALESCE(printer.price, laptop.price, pc.price)
FROM product
LEFT JOIN printer ON product.model = printer.model
LEFT JOIN laptop ON product.model = laptop.model
LEFT JOIN pc ON product.model = pc.model

What coalesce does is return the first non-NULL argument (so if printer.price is null and laptop.price isn't, it will return laptop.price).

Here is a good solution :

SELECT model, COALESCE(pr.price, la.price, pc.price) AS price
FROM product AS p
LEFT JOIN printer AS pr ON (p.model=pr.model)
LEFT JOIN laptop AS la ON (p.model=la.model)
LEFT JOIN pc  ON (p.model=pc.model)

I should notice that your table structure can be enhanced for your purpose and probably for the purpose of your application.

Instead of tables :

• Product (maker, model, type)
• Laptop (model, price, speed, ram, hd, screen)
• PC (model, price, speed, ram, hd)
• Printer (model,price, color)

It could be :

• Product (maker, model, type, price)
• Laptop (model, speed, ram, hd, screen)
• PC (model, speed, ram, hd)
• Printer (model, color)

Or even :

• Product (maker, type, price, reference, other_characteristics )

Where column [other_characteristics] contains the json of the other information.