EXPLANATION:

1. One project has a mobilization of crew to work on project
2. Mobilization action is created, with selected personnel/crew member
3. Each selected member has selected certificates relevant to the spesific project/mobilization


GOAL:

Get all persons in the mobilization crew, and the certificates selected/relevant for the mobilization of the spesific project.


-- QUERY

EXPLAIN SELECT u.id, u.first_name, c.title AS cert
FROM mobilization_crew_cert AS mc
JOIN certificates AS c ON c.id = mc.certificate_id
JOIN users AS u ON u.id = mc.user_id
JOIN mobilization_crew AS m ON m.mobilization_id = mc.mobilization_id

-- WHERE mc.mobilization_id = 1
GROUP BY mc.id



-- TABLES

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `username` varchar(15) default NULL,
  `password` varchar(40) default NULL,
  `created` datetime default '0000-00-00 00:00:00',
  `updated` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `first_name` varchar(50) default NULL,
  `last_name` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

(* project_id is from project table)

CREATE TABLE `mobilization` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `created` datetime default '0000-00-00 00:00:00',
  `updated` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `project_id` int(11) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

CREATE TABLE `mobilization_crew` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `created` datetime default '0000-00-00 00:00:00',
  `updated` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `mobilization_id` int(11) unsigned default NULL,
  `user_id` int(11) unsigned default NULL,
  `share_personalia` tinyint(1) unsigned default '0',
  PRIMARY KEY  (`id`),
  KEY `mobilization_id` (`mobilization_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

CREATE TABLE `mobilization_crew_cert` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `certificate_id` int(11) unsigned default NULL,
  `user_id` int(11) unsigned default NULL,
  `mobilization_id` int(11) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  KEY `certificate_id` (`certificate_id`),
  KEY `mobilization_id` (`mobilization_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

CREATE TABLE `certificates` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `user_id` int(11) unsigned default NULL,
  `title` varchar(255) default NULL,
  `body` text,
  `valid` tinyint(1) unsigned default '0',
  `valid_to` date default NULL,
  `notice` tinyint(1) unsigned default '0',
  `created` datetime default '0000-00-00 00:00:00',
  `updated` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8