Hi,
Please help writing the following query. There are 3 tables. I’m trying to display all records from table apptchart and matching records from appointments table as columns. I’ve pasted the table structure and some data.
Thanks in advance.
select timeslot, If(room = (select room from apptchartstaff where id = 1 and
Apptday regexp dayname(current_date)), fullname, ‘’) as Room1,
If(room = (select room from apptchartstaff where id = 2 and
Apptday regexp dayname(current_date)), fullname, ‘’) as Room2
from apptchart left join appointments on appointments.branchcode and apptchart.branchcode = ‘L’;
SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE IF EXISTS test;
CREATE DATABASE test
CHARACTER SET ‘latin1’
COLLATE ‘latin1_swedish_ci’;
USE test;
DROP TABLE IF EXISTS appointments;
CREATE TABLE appointments (
ID int(20) NOT NULL AUTO_INCREMENT,
BranchCode varchar(1) CHARACTER SET utf8 DEFAULT NULL,
ClientCode varchar(20) CHARACTER SET utf8 NOT NULL,
Room varchar(20) CHARACTER SET utf8 DEFAULT NULL,
ApptDate date NOT NULL,
ApptTime text NOT NULL,
Reason varchar(100) CHARACTER SET utf8 NOT NULL,
FullName varchar(255) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=256 DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS apptchart;
CREATE TABLE apptchart (
ID int(20) NOT NULL AUTO_INCREMENT,
BranchCode varchar(1) DEFAULT NULL,
TimeSlot text,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
DROP TABLE IF EXISTS apptchartstaff;
CREATE TABLE apptchartstaff (
ID int(20) NOT NULL AUTO_INCREMENT,
BranchCode varchar(1) NOT NULL,
ApptDay varchar(100) DEFAULT NULL,
Room varchar(20) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
INSERT INTO appointments (ID, BranchCode, ClientCode, Room, ApptDate, ApptTime, Reason, FullName) VALUES
(232,‘A’,‘T396080’,‘Room2’,‘2010-09-17’,‘07:00 AM’,‘Surgery’,‘test’),
(233,‘L’,‘L5267151’,‘Room1’,‘2010-11-18’,‘08:00 AM’,‘Extended’,‘test’),
(234,‘L’,‘L5267151’,‘Room1’,‘2010-09-17’,‘08:15 AM’,‘Extended’,‘test’),
(235,‘L’,‘B5465’,‘Room2’,‘2010-09-17’,‘08:30 AM’,‘Extended’,‘test’),
(236,‘L’,‘L5267151’,‘Room2’,‘2010-09-17’,‘08:45 AM’,‘Extended’,‘test’),
(237,‘L’,‘L5267151’,‘Room2’,‘2010-09-17’,‘09:00 AM’,‘Extended’,‘test’),
(238,‘L’,‘B9522’,‘Room1’,‘2010-09-17’,‘07:15 AM’,‘Standard Consultation’,‘Caramia AARNOUDSE’),
(239,‘A’,‘O3711’,‘Room1’,‘2010-09-17’,‘07:30 AM’,‘Surgery’,‘test’),
(240,‘L’,‘B17583’,‘Room1’,‘2010-09-17’,‘08:15 AM’,‘Dental’,‘test’),
(241,‘L’,‘T396080’,‘Room2’,‘2010-09-17’,‘07:15 AM’,‘Extended’,‘test’),
(242,‘L’,‘T396080’,‘Room2’,‘2010-09-17’,‘07:30 AM’,‘Extended’,‘test’),
(243,‘L’,‘O3711’,‘Room1’,‘2010-09-17’,‘07:45 AM’,‘Extended’,‘test’),
(244,‘L’,‘L5267151’,‘Room1’,‘2010-09-17’,‘08:15 AM’,‘Extended’,‘test’),
(245,‘L’,‘L5267151’,‘Room1’,‘2010-09-17’,‘08:30 AM’,‘Extended’,‘test’),
(246,‘L’,‘L5267151’,‘Room1’,‘2010-09-17’,‘08:45 AM’,‘Extended’,‘test’),
(247,‘L’,‘L5267151’,‘Room1’,‘2010-09-17’,‘09:00 AM’,‘Extended’,‘test’),
(248,‘L’,‘b17583’,‘Room1’,‘2010-10-26’,‘07:00 AM’,‘Standard Consultation’,‘rex SMITH’),
(250,‘L’,‘B9522’,‘Room1’,‘2010-10-27’,‘07:00 AM’,‘Standard Consultation’,‘Caramia AARNOUDSE’),
(251,‘L’,‘O9254976’,‘Room2’,‘2010-10-27’,‘07:30 PM’,‘Standard Consultation’,‘test’),
(252,‘L’,‘B36’,‘Room1’,‘2010-11-04’,‘07:00 AM’,‘Dental’,‘test’),
(253,‘L’,‘B9522’,‘Room1’,‘2010-10-27’,‘07:15 AM’,‘Extended’,‘Caramia AARNOUDSE’);
COMMIT;
INSERT INTO apptchart (ID, BranchCode, TimeSlot) VALUES
(1,‘L’,‘07:00 AM’),
(2,‘L’,‘07:15 AM’),
(3,‘L’,‘07:30 AM’),
(4,‘L’,‘07:45 AM’),
(5,‘L’,‘08:00 AM’),
(6,‘L’,‘08:15 AM’),
(7,‘L’,‘08:30 AM’),
(8,‘L’,‘08:45 AM’),
(9,‘L’,‘09:00 AM’),
(10,‘L’,‘09:15 AM’),
(11,‘L’,‘09:30 AM’),
(12,‘L’,‘09:45 AM’),
(13,‘L’,‘10:00 AM’),
(14,‘L’,‘10:15 AM’),
(15,‘L’,‘10:30 AM’),
(16,‘L’,‘10:45 AM’),
(17,‘L’,‘11:00 AM’),
(18,‘L’,‘11:15 AM’),
(19,‘L’,‘11:30 AM’),
(20,‘L’,‘11:45 AM’),
(21,‘L’,‘12:00 PM’),
(22,‘L’,‘12:15 PM’),
(23,‘L’,‘12:30 PM’),
(24,‘L’,‘12:45 PM’),
(25,‘L’,‘01:00 PM’),
(26,‘L’,‘01:15 PM’),
(27,‘L’,‘01:30 PM’),
(28,‘L’,‘01:45 PM’),
(29,‘L’,‘02:00 PM’),
(30,‘L’,‘02:15 PM’),
(31,‘L’,‘02:30 PM’),
(32,‘L’,‘02:45 PM’),
(33,‘L’,‘03:00 PM’),
(34,‘L’,‘03:15 PM’),
(35,‘L’,‘03:30 PM’),
(36,‘L’,‘03:45 PM’),
(37,‘L’,‘04:00 PM’),
(38,‘L’,‘04:15 PM’),
(39,‘L’,‘04:30 PM’),
(40,‘L’,‘04:45 PM’),
(41,‘L’,‘05:00 PM’),
(42,‘L’,‘05:15 PM’),
(43,‘L’,‘05:30 PM’),
(44,‘L’,‘05:45 PM’),
(45,‘L’,‘06:00 PM’),
(46,‘L’,‘06:15 PM’),
(47,‘L’,‘06:30 PM’),
(48,‘L’,‘06:45 PM’),
(49,‘L’,‘07:00 PM’),
(50,‘L’,‘07:15 PM’),
(51,‘L’,‘07:30 PM’),
(52,‘L’,‘07:45 PM’),
(53,‘L’,‘08:00 PM’);
COMMIT;
INSERT INTO apptchartstaff (ID, BranchCode, ApptDay, Room) VALUES
(1,‘L’,‘Sunday\rMonday\rTuesday\rWednesday\rThursday\rFriday \rSaturday’,‘Room1’),
(2,‘L’,‘Sunday\rMonday\rTuesday\rWednesday\rThursday\rFriday \rSaturday’,‘Room2’);
COMMIT;