Views: Old Testament and New Testament

Views are virtual tables and can save you much typing on common queries. They're new to MySQL 5.0, and pretty simple to learn.

The bible table includes both old and new testaments. Suppose you wanted to find lines about Adam, but limit your query to just the old testament. You could join on the books table like so...

     SELECT bb.id, book, cap, verse, line 

      FROM bible as bb

INNER JOIN books as bk ON (bb.book = bk.abbr)

     WHERE bk.volume = 'Old Testament'

       AND line LIKE '%Adam%';

.. which is fine for a one-off. But another approach is to create a view from the bible table containing just the old testament.

DROP VIEW IF EXISTS oldtest;
CREATE VIEW oldtest AS

     SELECT bb.id, book, cap, verse, line 

       FROM bible as bb

 INNER JOIN books as bk ON (bb.book = bk.abbr)

      WHERE bk.volume = 'Old Testament';

Now you can run a much simpler version of the above query like so:

SELECT * FROM oldtest WHERE line LIKE '%Adam%';

Pretty easy to type, huh? The oldtest view has already done the join to the books table for you.

Here's another view, this time for the new testament.

CREATE OR REPLACE VIEW newtest AS

     SELECT bb.id, book, cap, verse, line 

       FROM bible as bb

 INNER JOIN books as bk ON (bb.book = bk.abbr)

      WHERE bk.volume = 'New Testament';

So now you can do

SELECT * FROM newtest WHERE line LIKE '%Jesus%' ORDER BY RAND() LIMIT 3;

Did you notice I dispensed with my usual 'DROP VIEW IF EXISTS blah' line at the start of the above fragment. That's because view syntax (uniquely in MySQL) allows you to CREATE OR REPLACE, which has the same effect. Wish they had that syntax all over.

More fun with Views

The Apocrypha is in a separate table to the rest of the bible, called apoc. Now suppose you want your bible queries to include the apocrypha, rather than doing two separate queries, or one large clumsy UNION query, you could create the following view which squidges the bible and apoc tables together.

CREATE OR REPLACE VIEW bigbible AS

   SELECT * FROM bible 

    UNION 

   SELECT * FROM apoc;

Easy. Now you can SELECT * FROM bibgbible WHERE... and everything is there.