13 May 2009 @ 01:16PM

Updated: 25 Jan 2010 @ 01:17PM
On the previous page we showed how to get the userid out of the log table and then do a second query against the user table to retrieve the username, first name and last name. It is possible to get all this information in a single query. To do so we can query the data by joining the two tables together. Allow me to demonstrate.

Our First Join

In the above example I'm selecting the fields logid, log.userid, [action], logDate, ip, username, firstName and lastName. This is being selected from the table and then I'm joining the table users to log based on the users.userid column being equal to the log.userid column. The log.userid and users.userid refers to the specific columns... by having log.userid it means I'm referring to the userid column that's in the log table. Conversely, users.userid means I'm referring to the userid column in the users table. Since there are two identically named columns in the two table, if I don't specify which column I'm referring to I'll get an error.

Our First Join

What's happening here is that I'm doing a select from the log table. I'm saying also that the users table is directly related to the log table. The relation is based off of the userid field on the two tables. This then lets me select fields across both tables. This idea can be extended to include more tables or do even more complicated things. The query can also be rewritten some.

Our First Join

Here I've done two things. First, I've aliased log and users. Notice the a and b behind them in the from clause? This is an alias. Imagine if the table were a really long name, like userLogFilesDailyInterval and currentUserTable. Rather than typing a rather long field name like userLogFilesDailyInterval.userid, by aliasing we can shorten it to a.userid. The alias can be anything you want. I prefer to use letters because it's fast, but in some cases it makes more sense to alias it as something meaningful, perhaps usertable.userid. The tables in our database aren't especially long, but I'll continue to use aliasing because I so desire.

The second thing we did was get rid of the JOIN and ON words. Instead I'm just selecting from the two tables. In the WHERE clause I then create our table relationships. This is the same thing as explicitly using JOIN. Feel free to use whichever method you find the easiest to read and use.

Now that we've covered some basic joins, let's look at subqueries.
Comments (0)