-- 
-- Database: `fsmgroup3`
-- 
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `access`
-- 
 
DROP TABLE IF EXISTS `access`;
CREATE TABLE `access` (
  `access_id` INT(11) NOT NULL AUTO_INCREMENT,
  `access` VARCHAR(10) NOT NULL DEFAULT '',
  PRIMARY KEY  (`access_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `airport`
-- 
 
DROP TABLE IF EXISTS `airport`;
CREATE TABLE `airport` (
  `airport_id` INT(11) NOT NULL AUTO_INCREMENT,
  `airport_name` VARCHAR(100) NOT NULL DEFAULT '',
  `airport_code` CHAR(3) NOT NULL DEFAULT '',
  PRIMARY KEY  (`airport_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `service`
-- 
 
DROP TABLE IF EXISTS `service`;
CREATE TABLE `service` (
  `service_id` INT(11) NOT NULL AUTO_INCREMENT,
  `service` VARCHAR(25) NOT NULL DEFAULT '',
  PRIMARY KEY  (`service_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `title`
-- 
 
DROP TABLE IF EXISTS `title`;
CREATE TABLE `title` (
  `title_id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(4) NOT NULL DEFAULT '',
  PRIMARY KEY  (`title_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `userairportservices`
-- 
 
DROP TABLE IF EXISTS `userairportservices`;
CREATE TABLE `userairportservices` (
  `userairportservices_id` INT(11) NOT NULL AUTO_INCREMENT,
  `usr_id_users` INT(11) NOT NULL DEFAULT '0',
  `airport_id_airport` INT(11) NOT NULL DEFAULT '0',
  `service_id_service` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY  (`userairportservices_id`),
  KEY `usr_id_users` (`usr_id_users`),
  KEY `airport_id_airport` (`airport_id_airport`),
  KEY `service_id_service` (`service_id_service`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `users`
-- 
 
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `usr_id` INT(11) NOT NULL AUTO_INCREMENT,
  `usr_access` INT(11) NOT NULL DEFAULT '0',
  `usr_title` INT(11) DEFAULT NULL,
  `usr_fname` VARCHAR(75) NOT NULL DEFAULT '',
  `usr_lname` VARCHAR(75) NOT NULL DEFAULT '',
  `usr_add1` VARCHAR(75) NOT NULL DEFAULT '',
  `usr_add2` VARCHAR(75) DEFAULT NULL,
  `usr_add3` VARCHAR(75) DEFAULT NULL,
  `usr_city` VARCHAR(75) NOT NULL DEFAULT '',
  `usr_prov_state` CHAR(2) NOT NULL DEFAULT '',
  `usr_pcode` VARCHAR(10) NOT NULL DEFAULT '',
  `usr_cntry` VARCHAR(75) NOT NULL DEFAULT '',
  `usr_acode` VARCHAR(4) NOT NULL DEFAULT '',
  `usr_phone` VARCHAR(15) NOT NULL DEFAULT '',
  `usr_email` VARCHAR(75) NOT NULL DEFAULT '',
  `usr_pass` VARCHAR(12) NOT NULL DEFAULT '',
  PRIMARY KEY  (`usr_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

SELECT usr_id_users as USR_ID, airport_id_airport AS AIRPORT_ID,
a.airport_code AS AIRPORT_CODE,
max(if(service_id_service=1, 1, 0)) as JET,
max(if(service_id_service=2, 1, 0)) as GROUND,
max(if(service_id_service=3, 1, 0)) as GLYCOL
FROM userairportservices uas
INNER JOIN airport a
 on a.airport_id = uas.airport_id_airport
-- change this for different users
WHERE usr_id_users = 1 
GROUP BY airport_id_airport;