Re: create view not working
Aaron, why doesn't this work? the select works perfectly Define doesn't work? What is the error message? create view cost_report as SELECT c_name, d_manuf as Manufacturer, d_model as Model, count(1) as Number Enrolled, d_price as Monthly Price, count(1)*d_price as Cost FROM `b_devices` A left join b_device_types B on A.d_id = B.d_id left join b_prices C on A.d_id = C.d_id AND A.c_id = C.c_id left join b_company D on A.c_id = D.c_id group by A.d_id, A.c_id order by c_name Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW xxxx
On Monday 17 July 2006 13:26, Karl Larsen wrote: I'm using mysql on Linux and all is going well but I can't find the proper way to write a CREATE VIEW. I'm using 4.1 and is this function still in mysql? If the MySQL manual for 4.1 does not list views as a feature of that version, odds are it doesn't exist. Views are a V5+ feature. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create view and insert into problems
create view v_authornames as
select authorid, CONCAT(lastname,',',firstname)
from t_authors;
Or replace the middle term in the CONCAT function to whatever you want
to separate it -- ie, ' ' for a space, or just
CONCAT(lastname,firstname) to get output KritzerSheeri.
-Sheeri
On 6/25/06, Andreas Bauer [EMAIL PROTECTED] wrote:
Hello NG,
I have two tables in my mysql database
created with phpmyadmin:
t_authors:
1 authorid (primary key, auto_increment)
2 lastname
3 firstname
And a table named t_books, fields inside:
t_books:
1 bookid (primary key, auto_increment)
2 authorid (Typ:index, reference to authorid from t_authors done
with phpmyadmin)
3 title
4 subtitle
Now I want to create a view from t_authors, so that the fields
of lastname and firstname are one field with
the value inside: lastname, firstname:
create view v_authornames as
select authorid, lastname || ', ' || firstname
from t_authors;
But this view created only an empty field named
lastname ||', ' firstname. How can I join this two
fields so that I get one and this value?
There is another problem of me inserting values sequently in
the two tables:
insert into t_authors (lastname, firstname)
values ('Meyers', 'Scott');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Effektiv C++ Programmieren',
'50 Wege zur Verbesserung Ihrer Programme und Entwuerfe');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Mehr Effektiv C++ Programmieren',
'35 neue Wege zur Verbesserung Ihrer Entwuerfe und Programme');
insert into t_authors (lastname, firstname)
values ('Schlossnagle', 'George');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Advanced PHP Programming', 'A practical guide');
The problem is the authorid of t_books: which value should I take for
authorid of table t_books.
If I took '1', the referenced value of the authorid from t_authors was not
taken from the authorid from t_books, but always the value '1'?
If I took '0' or others I get errors.
In phpmyamin I set the reference to t_authors.authorid in the
t_books.authorid field.
Best regards and many thanks
Andreas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create view not working on mysql 4.1.8
I believe CREATE VIEW came in with 5.01, not 4.1.x. PB sirisha gnvg wrote: we are working on mysql 4.1.8 and windows XP platform. This version supports views.We created a view like this mysqluse sample mysqlcreate view v1 as select * from sam2; sam2 is a table We got an error errror 1024(42000):you have an error in your sql syntax; we checked the manual but the syntax is as written above.we also saw error 1024 in error notepad but we could not get any further details. please explain the fault in above statement Yahoo! India Matrimony: Find your life partneronline. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create view not working on mysql 4.1.8
sirisha gnvg wrote: we are working on mysql 4.1.8 and windows XP platform. This version supports views.We created a view like this mysqluse sample mysqlcreate view v1 as select * from sam2; sam2 is a table We got an error errror 1024(42000):you have an error in your sql syntax; we checked the manual but the syntax is as written above.we also saw error 1024 in error notepad but we could not get any further details. please explain the fault in above statement Yahoo! India Matrimony: Find your life partneronline. Why don't you upgrade to 5.0? I use create view in 5.0 without any problem. Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
In article [EMAIL PROTECTED], Martijn Tonies [EMAIL PROTECTED] writes: This is plain rubbish. See my other example with a more complicated view source. When adjusting the view, or extracting a script - the view source becomes complete gibberish. MS SQL, or Firebird, for example, store the view-source as defined - this includes comments, spacing etc etc... In short: it becomes usuable. MySQL should do this too. From reading these lists, I think MySQL only stores the resulting structure - or something similar - and (currently) not the view source. To make views useful, better change it... :-) Nope. A standards-compliant database is _required_ to store the structure of its objects in its internal information_schema, not some SQL string. Of course MySQL isn't standards-compliant at all, but we should not make it worse by imitating the quirks of other DBMSs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
Hello Harald, others, This is plain rubbish. See my other example with a more complicated view source. When adjusting the view, or extracting a script - the view source becomes complete gibberish. MS SQL, or Firebird, for example, store the view-source as defined - this includes comments, spacing etc etc... In short: it becomes usuable. MySQL should do this too. From reading these lists, I think MySQL only stores the resulting structure - or something similar - and (currently) not the view source. To make views useful, better change it... :-) Nope. A standards-compliant database is _required_ to store the structure of its objects in its internal information_schema, not some SQL string. Of course MySQL isn't standards-compliant at all, but we should not make it worse by imitating the quirks of other DBMSs. I'm unsure what to make of this statement -- I was asking if the view-source can be stored, so that it can be retrieved the way I created it. Do you agree or disagree? (and well, MySQL doesn't have an information schema yet, so that's a no-go area right now). With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
In article [EMAIL PROTECTED], Martijn Tonies [EMAIL PROTECTED] writes: Hello Harald, others, This is plain rubbish. See my other example with a more complicated view source. When adjusting the view, or extracting a script - the view source becomes complete gibberish. MS SQL, or Firebird, for example, store the view-source as defined - this includes comments, spacing etc etc... In short: it becomes usuable. MySQL should do this too. From reading these lists, I think MySQL only stores the resulting structure - or something similar - and (currently) not the view source. To make views useful, better change it... :-) Nope. A standards-compliant database is _required_ to store the structure of its objects in its internal information_schema, not some SQL string. Of course MySQL isn't standards-compliant at all, but we should not make it worse by imitating the quirks of other DBMSs. I'm unsure what to make of this statement -- I was asking if the view-source can be stored, so that it can be retrieved the way I created it. Do you agree or disagree? I disagree. A proper information_schema implementation is much more than a stored SQL string - it's a standardized way to access meta information about your DB objects. Thus it must parse the SQL DDL strings and store its meaning. Additionally storing the SQL string _as entered_ would be redundancy. SHOW CREATE VIEW could be implemented by reconstructing some standard representation of SQL DDL, but this would not always be exactly what you entered. On the other hand, omitting superfluous backticks (double quotes in standard SQL) from the output sounds reasonable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
Hello Harald, others, This is plain rubbish. See my other example with a more complicated view source. When adjusting the view, or extracting a script - the view source becomes complete gibberish. MS SQL, or Firebird, for example, store the view-source as defined - this includes comments, spacing etc etc... In short: it becomes usuable. MySQL should do this too. From reading these lists, I think MySQL only stores the resulting structure - or something similar - and (currently) not the view source. To make views useful, better change it... :-) Nope. A standards-compliant database is _required_ to store the structure of its objects in its internal information_schema, not some SQL string. Of course MySQL isn't standards-compliant at all, but we should not make it worse by imitating the quirks of other DBMSs. I'm unsure what to make of this statement -- I was asking if the view-source can be stored, so that it can be retrieved the way I created it. Do you agree or disagree? I disagree. A proper information_schema implementation is much more than a stored SQL string - it's a standardized way to access meta information about your DB objects. Agreed. For a view, it might store the resulting column datatypes etc... Thus it must parse the SQL DDL strings and store its meaning. Additionally storing the SQL string _as entered_ would be redundancy. Disagreed. For code, formatting has a reason. Returning some kind of constructed DDL is not the same. Code formatting usually is according to ones personal standards or company standards (eg: writing keywords in upper case, like SELECT). SHOW CREATE VIEW could be implemented by reconstructing some standard representation of SQL DDL, but this would not always be exactly what you entered. Disagreed - see above. How would you like your stored procedure source code to be returned? Stripped from comments and newlines or tabs? Guess not. On the other hand, omitting superfluous backticks (double quotes in standard SQL) from the output sounds reasonable. IMO, column names should be either backticked or only backticked if they are reserved words.. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
On 05 Oct 2004 11:46:18 +0200, Harald Fuchs wrote: Martijn Tonies writes: MS SQL, or Firebird, for example, store the view-source as defined - this includes comments, spacing etc etc... In short: it becomes usuable. MySQL should do this too. From reading these lists, I think MySQL only stores the resulting structure - or something similar - and (currently) not the view source. To make views useful, better change it... :-) Nope. A standards-compliant database is _required_ to store the structure of its objects in its internal information_schema There is no data stored in the INFORMATION_SCHEMA at all: quote 4.2 Introduction to the Information Schema. The views of the Information Schema are viewed tables /quote ISO/IEC 9075-11:2003 not some SQL string. The VIEWS view in the INFORMATION_SCHEMA is derived from the VIEWS base table in the DEFINITION_SCHEMA. Part of the definition of the that reads: quote 6.66 VIEWS base table (..) Definition CREATE TABLE VIEWS ( TABLE_CATALOGINFORMATION_SCHEMA.SQL_IDENTIFIER, TABLE_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER, TABLE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER, VIEW_DEFINITION INFORMATION_SCHEMA.CHARACTER_DATE, /quote ISO/IEC 9075-11:2003 So what would VIEW_DEFINITION store exactly if not the query expression that defines a view? Since the is no requirement to have an accessible DEFINITION_SCHEMA there may be a mechanism to recreate the definition on the fly from other information, but the same goes for the other view related base tables in the DEFINITION_SCHEMA. I see no requirement to store only the structure and not the SQL string. While I don't really care about the way the structure of a view is returned, I would very much like for it to be without those backticks. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
On 05 Oct 2004 12:02:44 +0200, Harald Fuchs wrote: Martijn Tonies [EMAIL PROTECTED] writes: I was asking if the view-source can be stored, so that it can be retrieved the way I created it. Do you agree or disagree? I disagree. A proper information_schema implementation is much more than a stored SQL string - it's a standardized way to access meta information about your DB objects. Thus it must parse the SQL DDL strings and store its meaning. Additionally storing the SQL string _as entered_ would be redundancy. No, it is not redundant. If I look at the information in the VIEW_COLUMN_USAGE, VIEW_ROUTINE_USAGE, VIEW_TABLE_USAGE and VIEWS views in the INFORMATION_SCHEMA (or their equivalent base tables in the DEFINITION_SCHEMA), they do not contain sufficient information to reconstruct anything but trivial views. SHOW CREATE VIEW could be implemented by reconstructing some standard representation of SQL DDL, but this would not always be exactly what you entered. I agree. One could imagine that views are stored in whatever format comes out of the parser so they are easily integrated into the execution plan at runtime. Obviously when you convert them back to a human readable query expression that query expression will be formatted differently. That seems perfectly reasonable to me. But since the INFORMATION_SCHEMA simply does not have the information required to reconstruct a view, you need something else too. And just as it would be perfectly reasonable to use the query tree, it would also be perfectly reasonable to take the original query expression for that. Both have advantages and disadvantages. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
In article [EMAIL PROTECTED], Jochem van Dieten [EMAIL PROTECTED] writes: Since the is no requirement to have an accessible DEFINITION_SCHEMA there may be a mechanism to recreate the definition on the fly from other information, but the same goes for the other view related base tables in the DEFINITION_SCHEMA. I see no requirement to store only the structure and not the SQL string. You're right, my wording was sloppy. What I meant is that information_schema must be able to _return_ (not _store_) information about the structure. In theory this could be achieved by storing the original SQL string only and parsing it on the fly, but this looks clumsy to me. While I don't really care about the way the structure of a view is returned, I would very much like for it to be without those backticks. ... unless required if you use strange column names. I agree on that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
Hai Martin, I suppose these are the ways with Databases. They don't work the way we like them to. So please adjust with it. For this matter I think, any RDBMS will be doing their or paddings to the scripts when they are loading it in memories. just the same way for oracle(where i have some (in)experience). so, don;'t worry, be happy. Regards, Eldo. On Wed, 29 Sep 2004 11:57:22 +0200, Martijn Tonies [EMAIL PROTECTED] wrote: Hi there, I'm testing MySQL 5.0.1 a bit - and I noticed the following... When executing this: create view myview2 (t0) as select c1 from t It returns this when doing a SHOW CREATE VIEW myview2: CREATE VIEW test.myview2 AS select `test`.`t`.`c1` AS `t0` from `test`.`t` This is not at all what I entered. I dislike backticks unless I specify them. I don't want a database name included unless I specify it (from `test`.`t`). Can the annoying behaviour be changed or turned off? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Regards, Eldo Skaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
Hello Eldo, I suppose these are the ways with Databases. They don't work the way we like them to. So please adjust with it. For this matter I think, any RDBMS will be doing their or paddings to the scripts when they are loading it in memories. just the same way for oracle(where i have some (in)experience). so, don;'t worry, be happy. This is plain rubbish. See my other example with a more complicated view source. When adjusting the view, or extracting a script - the view source becomes complete gibberish. MS SQL, or Firebird, for example, store the view-source as defined - this includes comments, spacing etc etc... In short: it becomes usuable. MySQL should do this too. From reading these lists, I think MySQL only stores the resulting structure - or something similar - and (currently) not the view source. To make views useful, better change it... :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
On Tue, 5 Oct 2004 00:27:45 +0530, Eldo Skaria wrote: I suppose these are the ways with Databases. They don't work the way we like them to. So please adjust with it. For this matter I think, any RDBMS will be doing their or paddings to the scripts when they are loading it in memories. But that doesn't make it right. Specifically, I seem to remember from the SQL standard that the use of database.table.field is undesired (or schema.table.field if you don't use MySQL) and instead database.table should be aliassed. In that case it should be: CREATE VIEW test.myview2 AS select alias.c1 AS t0 from test.t alias; Further, I would like to second Martijn's opinion regarding backticks. It is bad enough that the SQL standard double quote isn't used, let's not make it worse by including unwanted backticks. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
I suppose these are the ways with Databases. They don't work the way we like them to. So please adjust with it. For this matter I think, any RDBMS will be doing their or paddings to the scripts when they are loading it in memories. But that doesn't make it right. Specifically, I seem to remember from the SQL standard that the use of database.table.field is undesired (or schema.table.field if you don't use MySQL) and instead database.table should be aliassed. In that case it should be: CREATE VIEW test.myview2 AS select alias.c1 AS t0 from test.t alias; Further, I would like to second Martijn's opinion regarding backticks. It is bad enough that the SQL standard double quote isn't used, let's not make it worse by including unwanted backticks. I also mentioned a more real life example - I just realized I send that to the internals list, and not to this one. You can view my message here: http://lists.mysql.com/internals/17270 If I would be a user of MySQL 5 (and I am, working on a developer tool and all), I would be seriously ticked off if this would make it into the production release... Have a look at the message at the URL above and tell me this is the right way to bring a system into production and keep it maintainable. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW
Hi, I can't find this syntax in mysql tutorial and the program does not accept it. Well, that should give you a hint, shouldn't it? Is there another method of achieving this ? Views are going to be supported in MySQL 5, I believe. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CREATE VIEW
Hi, Subject: CREATE VIEW I can't find this syntax in mysql tutorial and the program does not accept it. Is there another method of achieving this ? About Views: http://www.mysql.com/doc/en/ANSI_diff_Views.html http://www.mysql.com/doc/pt/Unnamed_views.html Workarounds: With google you'll will easily find discussions about using temporary or heap tables as a workaround for views. It depends on your project, if that's applicable. Best Regards Michael Daheim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
