CREATE TABLE IF NOT EXISTS `teledata_staff` (
  `staff_id` int(11) NOT NULL,
  `last_name` varchar(30) DEFAULT NULL,
  `suffix` varchar(3) DEFAULT NULL,
  `name_title` varchar(10) DEFAULT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `middle` varchar(20) DEFAULT NULL,
  `empl_id` varchar(32) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `job_position` varchar(50) DEFAULT NULL,
  `bldg_id` int(11) DEFAULT NULL,
  `room` varchar(50) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  `email_machine` varchar(50) DEFAULT NULL,
  `postal` varchar(10) DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `listed` varchar(1) DEFAULT NULL,
  `cellphone` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`staff_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- View creation
-- Staff View
CREATE VIEW staff AS
SELECT
    teledata_staff.staff_id,
    teledata_staff.last_name,
    teledata_staff.first_name,
    teledata_staff.middle,
    teledata_staff.name_title,
    teledata_staff.suffix,
    teledata_staff.job_position,
    teledata_staff.room,
    teledata_staff.phone,
    CONCAT(teledata_staff.email, teledata_staff.email_machine) AS email,
    teledata_department.name AS department,
    teledata_department.dept_id,
    teledata_organization.name AS organization,
    teledata_organization.org_id,
    teledata_ucfbldg.name AS building,
    teledata_ucfbldg.bldg_id
FROM
    teledata_staff,
    teledata_department,
    teledata_organization,
    teledata_ucfbldg
WHERE
    teledata_staff.dept_id = teledata_department.dept_id AND
    teledata_staff.bldg_id = teledata_ucfbldg.bldg_id AND
    teledata_department.org_id = teledata_organization.org_id
;



-- Why come no match score?
SELECT
    MATCH(staff.first_name, staff.last_name) AGAINST ('Tom') as score,
    CONCAT(IFNULL(staff.first_name, ''), ' ', IFNULL(staff.last_name, '')) AS name,
    staff.email
FROM staff
WHERE staff.first_name = 'Tom'

score   name    email
0   Tom Carbone tcarbone@fiea.ucf.edu
0   Tom Hall    tvhall@mail.ucf.edu
0   Tom Hope    thope@mail.ucf.edu
0   Tom Campana tcampana@mail.ucf.edu
0   Tom Hurter  thurter@mail.ucf.edu
0   Tom Logsdon tlogsdon@mail.ucf.edu
0   Tom Mullin  tmullin@mail.ucf.edu
0   Tom O'Neal  oneal@mail.ucf.edu
0   Tom O'Neal  NULL
0   Tom O'Neal  oneal@mail.ucf.edu
0   Tom O'Neal  oneal@mail.ucf.edu
0   Tom Ehren   tehren@mail.ucf.edu
0   Tom O'Neal  NULL
0   Tom O'Neal  NULL
0   Tom Nelson  tanelson@mail.ucf.edu
0   Tom Owens   jtowens@mail.ucf.edu
0   Tom Owens   jtowens@mail.ucf.edu
0   Tom Snyder  tsnyder@athletics.ucf.edu
0   Tom O'Neal  oneal@mail.ucf.edu
0   Tom Owens   jtowens@mail.ucf.edu
0   Tom O'Neal  oneal@mail.ucf.edu