SHARE
TWEET

Untitled

a guest Jul 21st, 2017 497 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE IF NOT EXISTS `teledata_staff` (
  2.   `staff_id` int(11) NOT NULL,
  3.   `last_name` varchar(30) DEFAULT NULL,
  4.   `suffix` varchar(3) DEFAULT NULL,
  5.   `name_title` varchar(10) DEFAULT NULL,
  6.   `first_name` varchar(20) DEFAULT NULL,
  7.   `middle` varchar(20) DEFAULT NULL,
  8.   `empl_id` varchar(32) DEFAULT NULL,
  9.   `dept_id` int(11) DEFAULT NULL,
  10.   `job_position` varchar(50) DEFAULT NULL,
  11.   `bldg_id` int(11) DEFAULT NULL,
  12.   `room` varchar(50) DEFAULT NULL,
  13.   `phone` varchar(50) DEFAULT NULL,
  14.   `email` varchar(20) DEFAULT NULL,
  15.   `email_machine` varchar(50) DEFAULT NULL,
  16.   `postal` varchar(10) DEFAULT NULL,
  17.   `last_updated` datetime DEFAULT NULL,
  18.   `listed` varchar(1) DEFAULT NULL,
  19.   `cellphone` varchar(50) DEFAULT NULL,
  20.   PRIMARY KEY (`staff_id`)
  21. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  22.  
  23.  
  24. -- View creation
  25. -- Staff View
  26. CREATE VIEW staff AS
  27. SELECT
  28.     teledata_staff.staff_id,
  29.     teledata_staff.last_name,
  30.     teledata_staff.first_name,
  31.     teledata_staff.middle,
  32.     teledata_staff.name_title,
  33.     teledata_staff.suffix,
  34.     teledata_staff.job_position,
  35.     teledata_staff.room,
  36.     teledata_staff.phone,
  37.     CONCAT(teledata_staff.email, teledata_staff.email_machine) AS email,
  38.     teledata_department.name AS department,
  39.     teledata_department.dept_id,
  40.     teledata_organization.name AS organization,
  41.     teledata_organization.org_id,
  42.     teledata_ucfbldg.name AS building,
  43.     teledata_ucfbldg.bldg_id
  44. FROM
  45.     teledata_staff,
  46.     teledata_department,
  47.     teledata_organization,
  48.     teledata_ucfbldg
  49. WHERE
  50.     teledata_staff.dept_id = teledata_department.dept_id AND
  51.     teledata_staff.bldg_id = teledata_ucfbldg.bldg_id AND
  52.     teledata_department.org_id = teledata_organization.org_id
  53. ;
  54.  
  55.  
  56.  
  57. -- Why come no match score?
  58. SELECT
  59.     MATCH(staff.first_name, staff.last_name) AGAINST ('Tom') as score,
  60.     CONCAT(IFNULL(staff.first_name, ''), ' ', IFNULL(staff.last_name, '')) AS name,
  61.     staff.email
  62. FROM staff
  63. WHERE staff.first_name = 'Tom'
  64.  
  65. score   name    email  
  66. 0   Tom Carbone tcarbone@fiea.ucf.edu
  67. 0   Tom Hall    tvhall@mail.ucf.edu
  68. 0   Tom Hope    thope@mail.ucf.edu
  69. 0   Tom Campana tcampana@mail.ucf.edu
  70. 0   Tom Hurter  thurter@mail.ucf.edu
  71. 0   Tom Logsdon tlogsdon@mail.ucf.edu
  72. 0   Tom Mullin  tmullin@mail.ucf.edu
  73. 0   Tom O'Neal  oneal@mail.ucf.edu
  74. 0   Tom O'Neal  NULL
  75. 0   Tom O'Neal  oneal@mail.ucf.edu
  76. 0   Tom O'Neal  oneal@mail.ucf.edu
  77. 0   Tom Ehren   tehren@mail.ucf.edu
  78. 0   Tom O'Neal  NULL
  79. 0   Tom O'Neal  NULL
  80. 0   Tom Nelson  tanelson@mail.ucf.edu
  81. 0   Tom Owens   jtowens@mail.ucf.edu
  82. 0   Tom Owens   jtowens@mail.ucf.edu
  83. 0   Tom Snyder  tsnyder@athletics.ucf.edu
  84. 0   Tom O'Neal  oneal@mail.ucf.edu
  85. 0   Tom Owens   jtowens@mail.ucf.edu
  86. 0   Tom O'Neal  oneal@mail.ucf.edu
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top