GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Selecting rows as columns

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Selecting rows as columns

    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;

  • #2
    Sorry buddy, wrong forum!

    Here we specialize on _performance_ related questions for MySQL not generic SQL questions.

    And especially since you have cross posted this question _here_ _and_ on _sitepoint_ _and_ on _devshed_ I think you have shown that you don't value the time of others.

    Comment

    Working...
    X