# MySQL-Front 5.1 (Build 1.48)
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='STRICT_ALL_TABLES' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;
# Host: andlinux Database: hilis_new
# ------------------------------------------------------
# Server version 5.0.45-Debian_1ubuntu3.4
#
# Source for table case
#
CREATE TABLE `case` (
`patient_id` int(11) unsigned NOT NULL,
`location_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`patient_id`,`location_id`),
KEY `location` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Dumping data for table case
#
INSERT INTO `case` VALUES (1,1);
INSERT INTO `case` VALUES (1,2);
#
# Source for table case_unit_number
#
CREATE TABLE `case_unit_number` (
`case_patient_id` int(11) unsigned NOT NULL,
`case_location_id` int(11) unsigned NOT NULL,
`unit_number` varchar(255) NOT NULL,
PRIMARY KEY (`case_patient_id`,`case_location_id`,`unit_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Dumping data for table case_unit_number
#
INSERT INTO `case_unit_number` VALUES (1,1,'1011');
INSERT INTO `case_unit_number` VALUES (1,2,'1111');
#
# Source for table clinician_region
#
CREATE TABLE `clinician_region` (
`region_id` int(11) unsigned NOT NULL default '0',
`referrer_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`region_id`,`referrer_id`),
KEY `referrer` (`referrer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Dumping data for table clinician_region
#
INSERT INTO `clinician_region` VALUES (1,1);
#
# Source for table general_practice
#
CREATE TABLE `general_practice` (
`location_id` int(11) unsigned NOT NULL,
`address` varchar(255) default '',
`postal_code` varchar(10) NOT NULL,
PRIMARY KEY (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Dumping data for table general_practice
#
INSERT INTO `general_practice` VALUES (2,'64, Zoo Lane','AB1 2CD');
#
# Source for table hospital
#
CREATE TABLE `hospital` (
`location_id` int(11) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`region_id` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`location_id`),
KEY `region_id` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Dumping data for table hospital
#
INSERT INTO `hospital` VALUES (1,'Leeds General Infirmary',1);
#
# Source for table hospital_posting
#
CREATE TABLE `hospital_posting` (
`referrer_id` int(11) unsigned NOT NULL,
`hospital_id` int(11) unsigned NOT NULL,
`speciality_national_code` int(11) unsigned NOT NULL,
KEY `referrer_posting` (`referrer_id`,`hospital_id`),
KEY `speciality` (`speciality_national_code`),
KEY `hospital` (`hospital_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Dumping data for table hospital_posting
#
INSERT INTO `hospital_posting` VALUES (1,1,100);
#
# Source for table location
#
CREATE TABLE `location` (
`id` int(11) unsigned NOT NULL auto_increment,
`organisation_code` char(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
#
# Dumping data for table location
#
INSERT INTO `location` VALUES (1,'RR801');
INSERT INTO `location` VALUES (2,'B10000');
#
# Source for table patient
#
CREATE TABLE `patient` (
`id` int(11) unsigned NOT NULL auto_increment,
`last_name` varchar(25) NOT NULL,
`first_name` varchar(30) NOT NULL,
`dob` date default NULL,
`gender` enum('M','F','U') NOT NULL,
`nhs_number` varchar(10) default NULL,
`time` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
#
# Dumping data for table patient
#
INSERT INTO `patient` VALUES (1,'green','alan','1966-02-01','M','1111111111',NULL);
#
# Source for table referrer
#
CREATE TABLE `referrer` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) default NULL,
`national_code` varchar(8) default NULL,
`referrer_type` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
#
# Dumping data for table referrer
#
INSERT INTO `referrer` VALUES (1,'Brown, CC','C0123456','clinician');
INSERT INTO `referrer` VALUES (2,'Green, LL','G1213456','practitioner');
#
# Source for table referrer_posting
#
CREATE TABLE `referrer_posting` (
`referrer_id` int(11) unsigned NOT NULL default '0',
`location_id` int(11) unsigned NOT NULL default '0',
`created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`inactivated_at` datetime default NULL,
`active` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`referrer_id`,`location_id`),
KEY `location` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Dumping data for table referrer_posting
#
INSERT INTO `referrer_posting` VALUES (1,1,'2009-06-03 11:08:45',NULL,1);
INSERT INTO `referrer_posting` VALUES (2,2,'2009-06-03 11:38:29',NULL,1);
#
# Source for table region
#
CREATE TABLE `region` (
`id` int(11) unsigned NOT NULL auto_increment,
`code` varchar(3) NOT NULL,
`description` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
#
# Dumping data for table region
#
INSERT INTO `region` VALUES (1,'RR8','Leeds');
#
# Source for table request
#
CREATE TABLE `request` (
`id` int(11) unsigned NOT NULL auto_increment,
`created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`year` int(5) NOT NULL,
`request_number` int(5) NOT NULL,
`case_location_id` int(11) unsigned NOT NULL,
`case_patient_id` int(11) unsigned NOT NULL,
`case_unit_number_unit_number` varchar(255) NOT NULL,
`referrer_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `lab_number` (`year`,`request_number`),
KEY `referrer_posting` (`case_location_id`,`referrer_id`),
KEY `case_unit_number` (`case_patient_id`,`case_location_id`,`case_unit_number_unit_number`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
#
# Dumping data for table request
#
INSERT INTO `request` VALUES (1,'2009-06-03 11:08:17',2009,1,1,1,'1011',1);
INSERT INTO `request` VALUES (2,'2009-06-03 11:38:54',2009,2,2,1,'1111',2);
#
# Source for table speciality
#
CREATE TABLE `speciality` (
`national_code` int(11) unsigned NOT NULL,
`description` varchar(255) NOT NULL,
PRIMARY KEY (`national_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Dumping data for table speciality
#
INSERT INTO `speciality` VALUES (100,'General Surgery');
INSERT INTO `speciality` VALUES (600,'General Practice');
#
# Foreign keys for table case
#
ALTER TABLE `case`
ADD CONSTRAINT `case_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`),
ADD CONSTRAINT `case_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`);
#
# Foreign keys for table case_unit_number
#
ALTER TABLE `case_unit_number`
ADD CONSTRAINT `case` FOREIGN KEY (`case_patient_id`, `case_location_id`) REFERENCES `case` (`patient_id`, `location_id`);
#
# Foreign keys for table clinician_region
#
ALTER TABLE `clinician_region`
ADD CONSTRAINT `clinician_region_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`),
ADD CONSTRAINT `clinician_region_ibfk_2` FOREIGN KEY (`referrer_id`) REFERENCES `referrer` (`id`);
#
# Foreign keys for table general_practice
#
ALTER TABLE `general_practice`
ADD CONSTRAINT `general_practice_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`);
#
# Foreign keys for table hospital
#
ALTER TABLE `hospital`
ADD CONSTRAINT `hospital_ibfk_2` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`),
ADD CONSTRAINT `hospital_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`);
#
# Foreign keys for table hospital_posting
#
ALTER TABLE `hospital_posting`
ADD CONSTRAINT `hospital_posting_ibfk_1` FOREIGN KEY (`referrer_id`, `hospital_id`) REFERENCES `referrer_posting` (`referrer_id`, `location_id`),
ADD CONSTRAINT `hospital_posting_ibfk_2` FOREIGN KEY (`speciality_national_code`) REFERENCES `speciality` (`national_code`),
ADD CONSTRAINT `hospital_posting_ibfk_3` FOREIGN KEY (`hospital_id`) REFERENCES `hospital` (`location_id`);
#
# Foreign keys for table referrer_posting
#
ALTER TABLE `referrer_posting`
ADD CONSTRAINT `location` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
ADD CONSTRAINT `referrer` FOREIGN KEY (`referrer_id`) REFERENCES `referrer` (`id`);
#
# Foreign keys for table request
#
ALTER TABLE `request`
ADD CONSTRAINT `request_ibfk_1` FOREIGN KEY (`case_location_id`, `referrer_id`) REFERENCES `referrer_posting` (`location_id`, `referrer_id`),
ADD CONSTRAINT `request_ibfk_2` FOREIGN KEY (`case_patient_id`, `case_location_id`, `case_unit_number_unit_number`) REFERENCES `case_unit_number` (`case_patient_id`, `case_location_id`, `unit_number`);
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
#explain
select
req.request_number,
req.year,
p.last_name,
p.first_name,
p.dob,
p.nhs_number,
req.case_unit_number_unit_number as 'unit_number',
ref.name as 'referrer',
#if ( ref.referrer_type = 'practitioner', concat(g.address, ', ', g.postal_code), h.name ) as 'source',
case when ref.referrer_type = 'practitioner' then concat(g.address, ', ', g.postal_code) else h.name end as 'source',
#h.name, #g.address,#ref.referrer_type,
l.organisation_code,
s.description as 'department'
from request req
join patient p on req.case_patient_id = p.id
join referrer_posting rp on rp.referrer_id = req.referrer_id
join referrer ref on rp.referrer_id = ref.id
join location l on rp.location_id = l.id
left join hospital h on h.location_id = rp.location_id
left join general_practice g on g.location_id = rp.location_id
left join hospital_posting hp on hp.referrer_id = rp.referrer_id
left join speciality s on hp.speciality_national_code = s.national_code
