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?
Product(maker, model, type)
Laptop(Model, price, speed, ram, hd, screen)
PC(model, price, speed, ram, hd)
Printer(model,price, color, price)
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).
codeand some other things like links. There's documentation - Brendan Long 2012-04-03 21:29
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 :
It could be :
Or even :
Where column [other_characteristics] contains the json of the other information.