SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  2. #2
    SitePoint Member
    Join Date
    May 2004
    Location
    Stockholm Sweden
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stop cross-posting!!!

    You have cross posted this question on _percona_ _and_ on _sitepoint_ _and_ on _devshed_.

    And by doing that you have shown that you don't value the time of others that might end up answering a question that somebody else already has answered for you.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    and on _dbforums_ too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •