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\rThursd ay\rFriday\rSaturday','Room1'),
(2,'L','Sunday\rMonday\rTuesday\rWednesday\rThursd ay\rFriday\rSaturday','Room2');
COMMIT;