Creating MySQL View using UNION

Question

I am trying to create a view for the following query.

SELECT DISTINCT
  products.pid        AS id,
  products.pname      AS name,
  products.p_desc     AS description,
  products.p_loc      AS location,
  products.p_uid      AS userid,
  products.isaproduct AS whatisit
FROM products
UNION
SELECT DISTINCT
  services.s_id       AS id,
  services.s_name     AS name,
  services.s_desc     AS description,
  services.s_uid      AS userid,
  services.s_location AS location,
  services.isaservice AS whatisit
FROM services

But not able to do so. I am using MySql query browser. The error I am getting is:

A view can only be created from an active resultset of SELECT command

Can someone please help me with this?

score 15 · Answer 1 · answered Feb 05 '09 at 09:34

CREATE VIEW vw_product_services AS
SELECT DISTINCT products.pid AS id,
                products.pname AS name,
                products.p_desc AS description,
                products.p_loc AS location,
                products.p_uid AS userid,
                products.isaproduct AS whatisit
           FROM products
          UNION
          SELECT DISTINCT services.s_id AS id,
                services.s_name AS name,
                services.s_desc AS description,
                services.s_uid AS userid,
                services.s_location AS location,
                services.isaservice AS whatisit
           FROM services

I tried this and it worked! Thanks everyone :)

Learning · Answer 2 · 2009-02-05T06:52:39.280

4

You might want to swith the order of userid and location in the second select. The column names should match 1 to 1 in all selects of the union.

EDIT : For query browser , as this points out "To create a view from a query, you must have executed the query successfully. To be more precise, the view is created from the latest successfully executed query, not necessarily from the query currently in the Query Area"

so you need to execute the query first before you create the view in query browser.

The error is from the query browser and not mysql.

edited Feb 05 '09 at 06:52

answered Feb 05 '09 at 06:38

Learning

8,029
3
35
46

@Learning, are you trying these suggestions? I didn't need to execute the query first. I executed some other query, then executed "CREATE VIEW ... " and it worked fine. – dkretz Feb 05 '09 at 07:03
no I'm not. I do not have access to mysql. I'm just trying to help. – Learning Feb 05 '09 at 07:06
Your link suggests you might be right - I don't have that query browser, but it works ok in sqlyog. – dkretz Feb 05 '09 at 07:12

score 1 · Answer 3 · answered Feb 05 '09 at 06:42

1

You have different types being unioned into the same column. (The names can be different, but the types have to be the same, or at least auto-castable.) But as @Learning points out, it looks like you've twisted the SELECT column enumerations.

Just in case, the proper syntax (which worked for me) is

CREATE VIEW myView 
AS  
SELECT ...

answered Feb 05 '09 at 06:42

dkretz

37,399
13
80
138

if column names are different what column name would be reported when we do a select * from the view? – Learning Feb 05 '09 at 06:44
The first one. Try it and see (which is what I did.) – dkretz Feb 05 '09 at 06:46
The UNION part is working fine.. But I when I try to create a view for this UNION. I'm getting the following error: A view can only be created from an active resultset of SELECT command I amd using MySql query browser – Feb 05 '09 at 06:48
1

oh .. ok. Sybase is less forgiving. Thanks for the clarification. – Learning Feb 05 '09 at 06:48
Hmmm ... I could create the union. In fact, I could create it with mixed data types. I googled for your error msg and found nothing, too. – dkretz Feb 05 '09 at 06:53
Are you able to create a view at all? Try it with just "CREATE VIEW vWhatever AS SELECT pid FROM products" and work forward from there. – dkretz Feb 05 '09 at 06:56
OK, just add pieces from there until you know which part makes it fail. – dkretz Feb 05 '09 at 07:06
The error message is in "QueryBrowser.pas", part of mysql-gui-tools. Looking into the source code, it is triggered by a) not having an active result set and b) the query having the wrong type. Does removing the "DISTINCT" make any difference? In any case, this is a bug in QueryBrowser, not in MySQL. – Tomalak Feb 05 '09 at 08:52

score 1 · Answer 4 · edited Dec 02 '18 at 02:45

1

Just a little remark about UNION. UNION only returns the distinct values of your resultset. So there is no need to use SELECT DISTINCT combined with a UNION. Probably better for performance to not use DISTINCT too.

More info on UNION can be found here: SQL UNION Operator

edited Dec 02 '18 at 02:45

kit

1,166
5
16
23

answered Feb 05 '09 at 11:24

score 0 · Answer 5 · answered Feb 05 '09 at 08:57

The error message is in "QueryBrowser.pas", part of mysql-gui-tools.

procedure TQueryBrowserForm.SQLCreateViewClick(Sender: TObject);
// ... 
begin
  if Assigned(ActiveResultset) and (ActiveResultset.ResultSet.query.query_type = MYX_QT_SELECT)then
    // ... 
  else
    ShowError('Creation error', _('A view can only be created from a active resultset of SELECT command.'), []);
end;

It is triggered by a) not having an active result set and b) the query having the wrong type.

Does removing the "DISTINCT" make any difference? In any case, this is a bug in QueryBrowser, rather than one MySQL. Creating the view directly in MySQL should suffice as a work-around.

Creating MySQL View using UNION

5 Answers5

Linked