Monday, July 23, 2007

Using Query of Queries to Join Data from Different Sources

The current application I am working on has to have user security based on a employee's badge number. The badge number and the employee name are already stored in an external database (Syteline) which I can access through a SQL driver. When a new employee is added to the application, the application only needs to store the employee's badge number. Other associated data is also stored such as the role the user is in (admin, supervisor, etc.). The problem I ran into was that I needed to get all the data for each employee (badge, name and role). Query of queries to the rescue. With a little help from Ben Nadel (thank you!) here is how I joined both queries:
<!--- Get a list of existing users from the database --->
<cfset existingUsersList = GetUsersList() />

<!--- Get a list of users from Syteline but ignore the users
that already exist in the application --->
<cfquery name="sytelineUsersList" datasource="ECDSiteDB">
select  lcase(Fname) as FirstName,
   lcase(Lname) as LastName,
   ltrim(rtrim("Emp-Num")) as BadgeNumber
from  pub.employee
where    ltrim(rtrim("Emp-Num")) <> ''
   ltrim(rtrim("Emp-Num")) in (#valuelist(existingUsersList.BadgeNumber)#)
order by LastName, FirstName

<!--- Join the two queries to get the user's name, badge and role name
The existingUsersList.BadgeNumber needs to be cast as a varchar since it's stored as
an interger in the application's Users table --->
<cfquery name="usersList" dbtype="query">
select  FirstName, LastName,
   existingUsersList.Name as RoleName
from  sytelineUsersList, existingUsersList
where  sytelineUsersList.BadgeNumber = cast(existingUsersList.BadgeNumber as varchar)
order by LastName, FirstName
// //]]>