3.5. Selecting Data

Now that we have one row of data in each of our two tables, let's run some queries. We'll use the SELECT statement to select the data that we want. To get all of the columns and rows from the books table, enter the following:

SELECT * FROM books;

The asterisk, which acts as a wildcard, selects all columns. We did not specify any criteria by which specific rows are selected, so all rows are displayed from the books table. To select specific columns and rows, we name the columns we want and add a WHERE clause to the end of our SELECT statement:

SELECT rec_id, title, description  FROM books WHERE genre = 'novel';

This SQL statement displays just the record identification number, the book's title, and the description of the book from the books table for all books where the genre column has a value of novel. The results will be more meaningful, of course, when we have data on more books in the database. So, let's assume that we've entered data for a few dozen more books, and proceed.

If we want to get a list of novels from the database along with the author's full name, we need to join the books table to the authors table. We can join the two tables like this:

SELECT books.rec_id, title, pub_year,         CONCAT(author_first, ' ', author_last) AS author FROM books, authors WHERE author_last = 'Vernon'    AND author_id = authors.rec_id;

Both tables have columns called rec_id, so we need to specify the table to which we're referring whenever we refer to rec_id and are joining both tables. We do this by inserting the name of the table followed by a dot as a separator and then the column name. You can see an example of this in the first line, where we're selecting the record identification number for each book. Notice also in the second line that we've employed a string function, CONCAT( ). With this function you can take bits of data and merge them together with text to form more desirable-looking output. In this case, we're taking the author's first name and pasting a space (in quotes) onto the end of it, and then the author's last name onto the end of that. The results will appear in the output display as one column, which we've given a column heading of author, as an alias using the keyword AS. In the FROM clause, we've named both tables, separated by a comma. If we had more tables that we wanted to string together, we would just add them to this comma-separated list in any order. In the WHERE clause, we've specified that we want data on books written by authors with the last name "Vernon" and where we're able to join a row for a book to a row for an author. The joining point for this SQL statement is in the last line: we're joining the author_id from books to the rec_id in authors. If the table did not contain books by Olympia Vernon, nothing would be displayed. If it did, but we had failed to enter a row of data for her in the authors table, we would not have a successful match between the two tables, and nothing would be displayed. The results of the previous query are as follows:

+--------+-------+----------+----------------+ | rec_id | title | pub_year | author         | +--------+-------+----------+----------------+ |      1 | Eden  | 2003     | Olympia Vernon | |      2 | Logic | 2003     | Olympia Vernon | +--------+-------+----------+----------------+

As you can see, a second book by Olympia Vernon was found and both have been displayed. The column heading was changed for the output of the author's name per the AS. We could change the column headings in the display for the other columns with the keyword AS, as well. The author alias can be reused in a SELECT statement, but not in the WHERE clause, unfortunately. You can find more information on AS in Chapter 4.