A materialized view is essentially a table that's built dynamically. In Oracle, there's a command to do it directly, but I do something similar in mySQL and just rebuild the table as needed. (typically through a cron job that fires each morning, as I deal a lot in data warehouse type systems, which mysql works rather well for) In mySQL you need to specify the view directly, whereas Oracle will do query rewriting if it realizes it can.

As for getting fields from two tables, yes, you just have to tell mysql how to join them. Here's an example of creating a view, (plain old view, which is basically just a way to alias the table joins and field lists)

CREATE VIEW myview AS
SELECT a.field1, a.field2, a.field3, b.*
FROM tablea a LEFT JOIN tableb b USING (id);

[download]

I'd suggest seeing the mysql documentation for:

There is a complex set of rules about trying to update directly through a view though... depending on your type of join, the database may not be able to figure out which record it actually needs to place the value, and you can end up with odd results. For what was described in the original post, however, it should be possible to update through a view to join the bibliography data, and everything else. (because it's a one-to-one relationship, and not a one-to-many)

In reply to Re^2: OT: MySQL - TEXT field efficiency? Do I include in table or separate? by jhourcle
in thread OT: MySQL - TEXT field efficiency? Do I include in table or separate? by TedPride

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.