sql-view

View or User Defined Function?

I have a scenario where I combine two tables into one (using UNION) and also JOIN data from some other tables. I can do this either with a VIEW or a UDF/User Defined Function. Considering that I have no view so far at all in my database, but quite a few UDF's for fulltext search and the like, I'm tempted to use a UDF for this scenari...

Dynamic column names

Is it possible to create a view (not stored procedure) with dynamic column names based on another table? For example: Code: CodeId|Description ------------------ 1|Title 2|Notes Data: DataId|Content|CodeId|GroupId ----------------------------- 1|Title1 | 1| 1 2|Note1 | 2| 1 3|Title2 | 1|...

Data from two tables into one view

Is it possible to grab data from two tables (that have the same fields) into one view. Basically, so the view sees the data as if it was one table. ...

Query with priority

I have a query that I cannot get to work right. I have 3 tables; Person, PersonProgram and Category. Person: ID, .... PersonProgram: ID, PersonID, Category, Code ... Category: ID, ... The Person table has 1 record for each person and the PersonProgram has multiple programs per person. There are 4 categories and I need to pull ...

speeding up mysql queries / mysql views in django

I use the following code to select popular news entries (by date) from the database: popular = Entry.objects.filter(type='A', is_public=True).extra(select = {'dpub': 'date(dt_published)'}).order_by('-dpub', '-views', '-dt_written', 'headline')[0:5] To compare the execution speeds of a normal query and this one I ran the following mysq...

mysql CREATE VIEW not working from mysql_query

I have a code to create VIEW in mysql database which is working fine on my local server. It creates and crop view normally. But on my Online server it gives error "CREATE VIEW command denied to user" for online database I manually create VIEW in Database form PHPmyAdmin [Myadmin is in localhost online] it creating, SO i have permissio...

Why can't SQL Server alter a view in a stored procedure?

I'm using MS SQL Server, and I'd like to alter a view from within a stored procedure, by executing something like "alter view VIEWNAME as ([some sql])". A few pages thrown up by google assert that this is not supported directly (and neither are related alter-table statements), but there are also examples of how to work around it using c...

Query to count the exact number of records in all tables

IHello, I'm trying to count for each table in my MySQL database how many records there are in it. All of my tables happen to be in InnoDB and this query SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'myschema'; is way much too much of an estimate (There are +846 records and it tells me there are only +-400) ...

Can a view return multiple result sets?

I would like to have a view that I could run return multiple result sets. Is this possible? If a view can not do this, then is there another object with High Visibility/Transparency within SQL Server that can achieve this? (I like using a view because I can easily see it and run it under the Views folder). A stored procedure is not rea...

Materialized views with MySQL

Emulated materialized views with MySQL has good performance? I'm learning how to do with this link thanks Correction: "Materialized views" to "Emulated materialized views". ...