On the last page we covered joins. Next we'll look at subqueries. Subqueries allow you to put a query inside a query. Not sure what I'm talking about? Let's start with an example.


A Subquery to Count Log Entries
Here I'm starting with a typical query, selecting the username, firstname and lastname from the users table. I'm then selecting another column, but this column is created by another query within the main query. This is a subquery. Here I'm taking a count(*) from the log table based on the userid. This counts the number of rows in the log tables, based on the userid for the current row. Satis has 1 row in the logs table. Pig has 2 and Ox has 2. Subqueries can be very handy for many reasons.


A Subquery to Find Recent Users
With the above query I'm retrieving the username, firstname, lastname, and email of any user that's had a log table entry since 1 Jan 2008. In the where clause I use the max(logDate) select to retrieve the date of the most recent log entry. I can then use this in the WHERE clause of the main SELECT statement to pull only those users that have had log entries since that date.

How far you can take this is entirely up to your creativity.