Home › SQL Queries

Subqueries: The Juicy Bits

Mon, 12/17/2007 - 16:35 — richard.selby

People love to know where all the juicy stuff is in the bible. So how about we create a table which points in the right direction? Note the use of the SET data type, on one of its rare outings.

DROP TABLE IF EXISTS juicybits;

CREATE TABLE juicybits(
  book        VARCHAR(32),
  cap         TINYINT UNSIGNED,
  verse_start TINYINT UNSIGNED,
  verse_end   TINYINT UNSIGNED,
  title       VARCHAR(255) NOT NULL,
  class       SET('prostitution','virgin',  
                  'insults','rape','slavery',
                  'willy','bear-maul','stoning')
);

The good thing about this table is it's very easy to insert a reference, so when someone tells you to check out Ezekiel 23:19-20, well, it's just a question of looking the book abbreviation and inserting it.

Let's populate our table with a few juicy bits. Thanks to David Wong and Owen Ball's The 9 Most Badass Bible Verses for the references.

INSERT INTO juicybits VALUES
('Eze', 23, 2, 20, 'Whoring after heathens', 'prostitution,willy'),
('2Ki',2,23,24,    'Who you callin\' bald-head?','insults,bear-maul'),
('Deu',22, 13, 30, 'One night stands', 'virgin,rape,stoning'),
('1Sm', 18, 25,30, 'One hundred Philistine Penises','willy')
;

Very easy to populate, but on the other hand with references stored like this, it's a pig to pull out and refer them back to the main bible table.

Suppose you want to see all the stuff about rape, first let's get the book/cap/verse ref from juicybits.

SELECT * FROM juicybits WHERE class LIKE '%rape%';
+------+------+-------------+-----------+------------------+---------------------+
| book | cap  | verse_start | verse_end | title            | class               |
+------+------+-------------+-----------+------------------+---------------------+
| Deu  |   22 |          13 |        30 | One night stands | virgin,rape,stoning |
+------+------+-------------+-----------+------------------+---------------------+

Now we need a query that uses the verse_start and verse_end and relates it back to the main bible.

SELECT id, line
  FROM bible
 WHERE book='Deu' AND cap=22
   AND verse >= 13
   AND verse <= 30;

# more simply, could use BETWEEN
SELECT id, line
  FROM bible
 WHERE book='Deu' AND cap=22
   AND verse BETWEEN 13 AND 30;

Obviously, I don't fancy manually typing in the book/cap/verse refs like this. How about with a suquery?

SELECT id, line FROM bible
WHERE book  =(SELECT book FROM juicybits WHERE class LIKE '%rape%')
  AND cap   =(SELECT cap FROM juicybits WHERE class LIKE '%rape%')
  AND verse BETWEEN (SELECT verse_start FROM juicybits WHERE class LIKE '%rape%')
                AND (SELECT verse_end FROM juicybits WHERE class LIKE '%rape%')    
;

SELECT * FROM bible
 WHERE id BETWEEN
              (SELECT b.id
                 FROM juicybits j
           INNER JOIN bible b
                   ON ( b.book = j.book
                       AND b.cap = j.cap
                       AND b.verse = j.verse_start)
                WHERE class = 'rape')
AND
             (SELECT b.id
                FROM juicybits j
          INNER JOIN bible b ON (b.book = j.book
                                 AND b.cap = j.cap
                                 AND b.verse = j.verse_end )
               WHERE class = 'rape')
ORDER BY id ;

It uses subqueries to relate the start verse and end verse to an id in the bible table. Not very elegant. There's got to be a better way.