Please see the first shallow dive for a primer on the DB tables, columns & terms. You can find that document here. As both of these documents only look at a few tables and present a few queries we are still playing in the kiddie pool, we aren't on the high dive yet.
There will occasions where when the SQL database needs to be queried for a variety of purposes. Cisco Call Manager 8.x uses an IBM Informix Database for storing data.
1. Using Aliases in SQL Statements
There will be times that you query fields from multiple tables. In order to do this, you need to use table.fieldname in the query so CM knows what field in which table you are querying. You can use the full table name or you can use an alias. I will show examples of both.
Note: The alias is one that the SQL Programmer (you) assigns and therefore can be anything.
Here are some simple queries.
run sql select d.name from device as d
run sql select device.name from device
run sql select eu.userid from enduser as eu
run sql select enduser.userid from enduser
run sql select rp.name,rp.description from routepartition as rp
run sql select routepartition.name,routepartition.description from routepartition
d.name = device table and name field.
eu.userid = enduser table and userid field.
rp.name = routepartition table and name field
rp.description = routepartition table and description field.
I know the above queries are fairly simple. Please understand that they are only to show the most simple example of how to use an alias. We will get more complicated in the next few examples.
2. Simple Query of Two Tables Using Alias
In this query, we query both the enduer and device tables and we use aliases.
We are assuming that the userid and the phone name is known in this query.
run sql select eu.firstname,eu.lastname,d.description from enduser as eu, device as d where eu.userid='tglen' and d.name='SEP3CCE73AC397F'
3. Using INNER JOIN to Display Data from Multiple Tables
The INNER JOIN keyword selects records that have matching values in both tables.
Here is where the fun starts. Basic INNER JOIN syntax is below