How to get Data from Multiple Database Tables ?

Aruba Employee

(Using Joins and Self-Joins in SQL)

Users with access to the CLI are able to run compound SQL statements to get specific data values. There's a few cases where the data wanted is spread between several tables. Here's a few example SQL commands:

Example 1:
Desired output of AP Name, Group Name, and Folder Name

Data table structure:
ap: id, name, ap_group_id, ap_folder_id
ap_group: id, name
ap_folder: id, name

SQL Method 1 - Calling the tables directly:
# SELECT
ap.name,
ap_group.name,
ap_folder.name
FROM
ap,
ap_group,
ap_folder
WHERE
ap.ap_group_id = ap_group.id
AND
ap.ap_folder_id = ap_folder.id;
--This is the most basic way of calling data from multiple tables, using the 'from' statement to call tables, and the 'where' statement to define joining parameters

SQL Method 2 - Asigning variables to the table names:
# SELECT
a.name,
b.name as AP,
c.name as CONTROLLER
FROM
ap a,
ap_group b,
ap_folder c
WHERE
a.ap_group_id = b.id
AND
a.ap_folder_id = c.id;
--This is an advanced way of joining where you assign variables to the tables names, and use the variables throughout. This is useful if you are using several tables that have long names


Example 2:
Getting more complex, perhaps you're looking for users connected to an AP and the controller that AP is connected to - this requires data pieces a table that refers to data within the same table (also known as a self-join statement)

Data table structure:
client_historical: id, username, mac, ssid, ap_id
ap: id, name, controller_id

# SELECT
ch.username,
ch.mac,
ch.ssid,
a.name as AP,
b.name as CONTROLLER
FROM
client_historical ch,
ap a,
ap b
WHERE
ch.ap_id = a.id
AND
b.id = a.controller_id
ORDER BY ch.mac;
--Note the usage of table name variables, and the self join within 'ap a' and 'ap b'

Same command, but filtering duplicate lines using 'GROUP BY':
# SELECT ch.username, ch.mac, ch.ssid, a.name, b.name
FROM client_historical ch, ap a, ap b
WHERE ch.ap_id = a.id
AND b.id = a.controller_id
GROUP BY ch.username, ch.mac, ch.ssid, a.name, b.name
ORDER BY ch.mac;
--Same command, just filtering out duplicate lines since the client_historical table is so large

Here's another of the same command, but with more data:
# SELECT
ch.username,
ch.role,
ch.mac,
ch.ssid,
ch.ap_radio_description,
ch.authen_type,
ch.lan_ip,
ch.lan_hostname,
a.name as AP,
b.name as CONTROLLER
FROM
client_historical ch,
ap a,
ap b
WHERE
ch.ap_id = a.id
AND
b.id = a.controller_id
GROUP BY
ch.username,
ch.role, ch.mac,
ch.ssid,
ch.ap_radio_description,
ch.authen_type,
ch.lan_ip,
ch.lan_hostname,
a.name,
b.name
ORDER BY ch.mac;
--As you can see, for collecting lots of data, it's good to call tables using variables to keep the command short (imagine replacing all 'ch' with 'client_historical'


**Tip** Make sure to use logical variables so that you don't get mixed up

Version history
Revision #:
1 of 1
Last update:
‎06-26-2014 10:06 AM
Updated by:
 
Labels (1)
Contributors
Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: