SQL SELECT examples

I think SQL and CSS have something in common, on the first look they appear to be dead easy to learn and then, as you move on, you realize that things can get tough. This is because both CSS and SQL trying to resemble ordinary language, they remain syntactically loose. This is good and bad. You can get to the same result by following different paths, the compiler will not be extremely strict with you but, when you try to build something, you may stare at you monitor for a really long time before you start writing a single word.

Now, as long as Joomla stores all its data in a MySQL database, you definitely need a fundamental knowledge of SQL to get to the next level. SQL is useful for looking behind the scenes, for generating reports or for changing data on the fly. Not to say that it is vital if you look forward to creating your own Joomla extensions. In this article I will give you some basic SQL SELECT examples attempting to build difficulty gradually. All examples should work if run against a standard Joomla database so, go ahead and test them.

Please note that you may not be using that standard jos_ table prefix so, substitute yours in the following statements. To find the one you are using go to the administrator's backend: Site > Global Configuration > Server > Database Settings.

BASIC COMMANDS

Let's start with some basic SELECT statements:

Show all users:

SELECT * FROM jos_users

Show all published articles:

SELECT * FROM jos_content
WHERE state = '1'

Show all users except from user admin:

SELECT * FROM jos_users
WHERE username <> 'admin'

In the previous statements, * means "all columns", to show fewer columns just put down their names. Show only the name and the email of all users:

SELECT name, email FROM jos_users:

Show the title of all published sections:

SELECT title FROM jos_sections 
WHERE published = 1

SORTING DATA

You can use the "ORDER BY" clause to sort the resulting rows. Either ascending

SELECT * FROM jos_content
WHERE state ='1'
ORDER BY id ASC

Or descending

SELECT * FROM jos_content
WHERE state = '1'
ORDER BY created DESC

Note that you can use more than one columns to do the sorting.

SELECT * FROM jos_categories
ORDER BY published ASC, section ASC, id DESC

LIMIT OUTPUT

You can limit the number of output rows as in the following example.

SELECT * FROM jos_content
LIMIT 0, 10

LOGICAL OPERATORS

SQL supports logical operators that help you define multiple criteria.

Show all published articles that have been created after 2009-08-20 00:00:00.

SELECT * FROM jos_content
WHERE state = '1'
AND created > '2009-08-20 00:00:00'

You see how it builds up.

SELECT * FROM jos_content
WHERE state = '1' 
AND hits > '1000' 
AND hits < '2000'
AND created < '2009-08-20 00:00:00'

And of course, you may use parenthesis if you want to define precedence.

SELECT * FROM jos_content
WHERE (sectionid = '5' OR sectionid = '6')
AND created < '2009-08-20 00:00:00'
ORDER BY created DESC
LIMIT 0, 10

WILDCARDS

The other interesting thing SQL supports is wildcards, have a look at the following statements

Show all users whose username starts with "a"

SELECT *
FROM jos_users
WHERE username LIKE 'a%'

Show id, email, usertype from all users whose username ends with "n"

SELECT id,email,usertype
FROM jos_users
WHERE username LIKE '%n'

Show all users whose username ends with "m{any digit}n"

SELECT *
FROM jos_users
WHERE username LIKE '%m_n'

FUNCTIONS

Furthermore you have a good variety of functions available to perform operations on groups of data. Have a look at the following examples.

SELECT AVG(hits) FROM jos_content

SELECT COUNT(*) FROM jos_content

SELECT MAX(hits) FROM jos_content

SELECT SUM(hits) FROM jos_content

The "GROUP BY" statement can be used in conjunction with the previous functions to group the results. For example, to get the number of hits per section you would use:

SELECT sectionid, SUM(hits)
FROM jos_content
GROUP BY sectionid

Now, another way to limit the output record set is with the "HAVING" clause combined with one of the basic functions.

SELECT sectionid, SUM(hits)
FROM jos_content
GROUP BY sectionid
HAVING SUM(hits)>5000

NESTING

Let's complicate things a bit more, SQL supports nesting. Suppose you want the rows from a table that have an attribute that is defined in another table, what would you do? For example, after you check the jos_content_frontpage table, how would you select all articles that are not presented in the front page? The IN clause is used to define a set of records.

SELECT *
FROM jos_content
WHERE id NOT IN (SELECT content_id FROM jos_content_frontpage)

JOIN

JOINS are used when you need to fetch data from more than one table under a specific condition. For example, suppose you need to fetch all article titles from the database together with the corresponding category titles. The former reside in the jos_content table whereas the latter in the jos_categories table, the only connective element is the category id. Here is how it goes:

SELECT jos_categories.title, jos_content.title
FROM jos_content
JOIN jos_categories ON jos_content.catid = jos_categories.id
ORDER BY jos_categories.id

To avoid ambiguity and as long as we are selecting from more than one tables, the column notation is altered to "column"."table". Let's extend this with another example, suppose we want all articles, along with their category titles and the author usernames.

SELECT jos_categories.title, jos_content.title, jos_users.username
FROM jos_content
JOIN jos_categories ON jos_content.catid = jos_categories.id
JOIN jos_users ON jos_content.created_by = jos_users.id
ORDER BY jos_categories.id

And of course all these may be combined with our previous examples.

SELECT jos_categories.title, jos_content.title, jos_users.username
FROM jos_content
JOIN jos_categories ON jos_content.catid = jos_categories.id
JOIN jos_users ON jos_content.created_by = jos_users.id
WHERE jos_content.state = '1'
AND jos_content.hits > '10'
AND jos_content.id NOT IN (SELECT content_id FROM jos_content_frontpage) 
ORDER BY jos_categories.id

Well, this is it for now, in the near future I will extend this article with the various JOIN types and I will write another article with more complicated examples extracted from "real" Joomla extensions.

Good luck!