MySql Joining three relations and performing query on that result

Go To StackoverFlow.com

0

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)

2012-04-03 21:04
by NoName
you can use inner join - Teja 2012-04-03 21:06
Real quick how do I originally put my code as a block? [code] - NoName 2012-04-03 21:07
@BumSkeeter - Indent it by 4 spaces - Brendan Long 2012-04-03 21:09
The first line or each code, will it format it too - NoName 2012-04-03 21:16
Each line of code needs to be indented by 4 spaces. Look at the source code for this question to see how I formatted yours - Brendan Long 2012-04-03 21:17


0

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

2012-04-03 21:09
by Brendan Long
I ended up doing this: (SELECT model, price FROM product NATURAL JOIN pc WHERE maker = 'B') -> union -> (SELECT model, price FROM product NATURAL JOIN printer WHERE maker = 'B') -> union -> (SELECT model, price FROM product NATURAL JOIN laptop WHERE maker = 'B' - NoName 2012-04-03 21:25
I take it I cannot format a comment - NoName 2012-04-03 21:26
@BumSkeeter - You can put backticks around code and some other things like links. There's documentation - Brendan Long 2012-04-03 21:29
@BumSkeeter Doing unions was a good idea. I didn't think of it - Brendan Long 2012-04-03 21:31


0

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.

2012-04-03 21:20
by Skrol29
I am just trying to get through my first set of homework. This is my first time with MySql. It is very interesting. But thank you for trying to answer - NoName 2012-04-03 21:28