Creating a view from a union query

Question

I'm sorry to have to bring up this as well. But it doesn't seem like I am getting any luck today. I have concatenated all my results into "Unioned" and am now attempting to push this into a view. This should be simple as well, but I can't seem to shift various components around to get it to work. This is the code I am trying to use:

CREATE VIEW v AS
SELECT *
FROM
(
  (SELECT maker, model, price FROM product NATURAL JOIN laptop)
    UNION
  (SELECT maker, model, price FROM product NATURAL JOIN pc)
    UNION
  (SELECT maker, model, price FROM product NATURAL JOIN printer)
) `Unioned`

Error: #1349 - View's SELECT contains a subquery in the FROM clause

I have been trying to encapsulate various components into parenthesis. Or create a new statement just for creating the view. This question should be fairly simple to answer, but I'm just not seeing it.

Answer 1

There's a decent chance this will work — if your DBMS allows union queries in views.

CREATE VIEW v AS
    SELECT maker, model, price FROM product NATURAL JOIN laptop
    UNION
    SELECT maker, model, price FROM product NATURAL JOIN pc
    UNION
    SELECT maker, model, price FROM product NATURAL JOIN printer

You might want to consider UNION ALL instead of UNION (aka UNION DISTINCT) because UNION DISTINCT will almost certainly be considerably slower, especially if the tables are big. On the other hand, you may prefer to do without duplicates, in which case UNION is correct.

Answer 2

Try removing the sub-queries, I think this should work:

CREATE VIEW v AS
SELECT maker, model, price FROM product NATURAL JOIN laptop
  UNION SELECT maker, model, price FROM product NATURAL JOIN pc
  UNION SELECT maker, model, price FROM product NATURAL JOIN printer