List SQL

The List SQL field will accept any standard SQL queries, including sorting and joins. Use the following notation to select database elements:

[_TableName].[FieldName]

For example, the following query will display employees who have been assigned to desks.

SELECT [_Employees].[LastName], [_Employees].[FirstName], [_Desks].[deskname], [_Floors].[FloorName]  FROM [_Employees] INNER JOIN [_Desks] ON [_Employees].[Desk_id]=[_Desks].[desk_id]INNER JOIN [_Floors] ON [_Employees].[Floor_Id]=[_Floors].[floor_id]

You can learn more about SQL queries at w3schools.com.

Examples

Employee Lists

Firstname, Lastname, Security Group (getting security group names from the _SecurityGroups table):

SELECT [_Employees].[FirstName], [_Employees].[LastName], [_SecurityGroups].[Security Group] FROM [_Employees] LEFT JOIN [_SecurityGroups] ON [_Employees].[SecurityGroup_id] = [_SecurityGroups].[SecurityGroup_id]

Firstname, lastname, desk (getting deskname from the _Desks table):

SELECT [_Employees].[FirstName], [_Employees].[LastName], [_Desks].[deskname] FROM [_Employees] LEFT JOIN [_Desks] ON [_Employees].[Desk_id] = [_Desks].[desk_id]

Full name, department (merging firstname & lastname):

SELECT [FirstName] + ' ' + [LastName] AS Employee, [department] FROM [_Employees]

Full name, floor, desk (getting deskname from the _Desks table and floorname from the _Floors table):

SELECT [_Employees].[FirstName] + ' ' + [_Employees].[LastName] AS Employee, [_Floors].[FloorName], [_Desks].[deskname] FROM [_Employees] LEFT JOIN [_Floors] ON [_Employees].[Floor_Id] = [_Floors].[floor_id] LEFT JOIN [_Desks] ON [_Employees].[Desk_id] = [_Desks].[desk_id]

Firstname, lastname, department, position, email, extension:

SELECT [FirstName], [LastName], [department], [position], [Email], [Extension] FROM [_Employees]

List only employees in the IT Department:

SELECT [LastName], [FirstName], [department] FROM [_Employees] WHERE [department] = 'IT'

List only employees in NY (using state field):

SELECT [LastName], [FirstName], [State] FROM [_Employees] WHERE [State] = 'NY'

List only employees on Floor 1:

SELECT [_Employees].[LastName], [_Employees].[FirstName], [_Floors].[FloorName] FROM [_Employees] LEFT JOIN [_Floors] ON [_Employees].[Floor_Id] = [_Floors].[floor_id] WHERE [_Floors].[FloorName] = 'Floor 1'

Desk Lists

Desk name, floor name:

SELECT [_Desks].[deskname], [_Floors].[FloorName] FROM [_Desks] LEFT JOIN [_Floors] ON [_Desks].[floor_id] = [_Floors].[floor_id]

Desk name, seats:

SELECT [deskname], [Seats] FROM _Desks

List only meeting rooms (based on category):

SELECT [deskname], [Category] FROM [_Desks] WHERE [category] = 'Meeting Room'

List only desks on Floor 1:

SELECT [name] FROM {_Desks] WHERE [floor_id] = '201804280603417960'

SELECT [_Desks].[deskname], [_Floors].[FloorName] FROM [_Desks] LEFT JOIN [_Floors] ON [_Desks].[floor_id] = [_Floors].[floor_id] WHERE [_Floors].[FloorName] = 'Floor 1'

Asset Lists

Asset name, floor name:

SELECT [_Assets].[name], [_Floors].[FloorName] FROM [_Assets] LEFT JOIN [_Floors] ON [_Assets].[floor_id] = [_Floors].[floor_id]

Asset name, model:

SELECT [name],[Model] FROM [_Assets]

List only printers (based on category):

SELECT [name],[category] FROM [_Assets] WHERE [category] = 'Printer'

List only assets on Floor 1:

SELECT [name] FROM {_Assets] WHERE [floor_id] = '201804280603417960'

SELECT [_Assets].[name], [_Floors].[FloorName] FROM [_Assets] LEFT JOIN [_Floors] ON [_Assets].[floor_id] = [_Floors].[floor_id] WHERE [_Floors].[FloorName] = 'Floor 1'