MySQL Views Help?

[random1]

I'm trying to create a 'view' in MySQL that grabs statistics on multiple tables within my database.

 

The statistics inlcude things like: "the total number of users", "total number of products" etc...

 

View's Body:

 

select 
  count(`total number of users`.`user_id`) AS `COUNT(``user_id``)` 
from `user` `total number of users`;

 

Can I do multiple SELECT statements in a view? If not how can I select data from more than 1 table in a view? Am I missing something obvious?

 

- Dean

[JustLikeIcarus]

A view is basically just the result of a select statement.  So once your query is returning the data how you want then you just do

create view myView as (put your sql here)

 

If you want your columns to be made up of the results of different queries there are a couple was to do this.  The easiest to maintain would be something like

select q1.result as colname1, q2.result as colname2
from 
(select count(*) from some_table) q1, 
(select count(*) from some_table) q2

 

Hope that made sense.

[random1]

I tried:

 

select q1.result as colname1, q2.result as colname2
from
(select count(`product_id`) from `product`) q1,
(select count(`user_id`) from `user`) q2

 

but get the error "SQL Error (1054): Unknown column 'q1.result' in 'field list'".

[JustLikeIcarus]

oh sorry i forgot to name them in the queries

 

select q1.result as colname1, q2.result as colname2
from
(select count(`product_id`) as result from `product`) q1,
(select count(`user_id`) as result from `user`) q2

 

[random1]

I tried:

 

SELECT q1.result AS total_users, q2.result AS total_products
FROM
(SELECT count(`user_id`) AS result FROM `user`) q1,
(SELECT count(`product_id`) AS result FROM `product`) q2;

 

and it works as a normal SQL execution but not in a view.

 

Seems like 'views' do not support subqueires.

 

Can someone please confirm this?

[JustLikeIcarus]

From the mysql manual:

A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries. The SELECT need not even refer to any tables.

 

what error are you getting?

[random1]

"SQL Error (1349): View's SELECT contains a subquery in the FROM clause" for the code:

 

SELECT q1.result AS total_users, q2.result AS total_products
FROM
(SELECT count(`user_id`) AS result FROM `user`) q1,
(SELECT count(`product_id`) AS result FROM `product`) q2;

[JustLikeIcarus]

I did some searching and it looks like this is a bug in mySQL that hasn't been resolved. What version are you running?  Try the query this way. I know oracle allows subqueries as columns so mySQL should as well.

SELECT (SELECT count(`user_id`) FROM `user`) AS total_users, (SELECT count(`product_id`) FROM `product`) AS total_products

 

 

[random1]

Thanks JustLikeIcarus, that finally works great

 

You are full of awesomeness