Test SQL

Sql code posted
created at 29 Mar 23:52, updated at 01 Apr 16:07

Edit | Back 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
CREATE USER 'replicant'@'%' IDENTIFIED BY 'slavepass';
 GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%';

SELECT 'mediaid'             AS `idtype`, 
       `m`.`mediaid`         AS `id`, 
       `m`.`title`           AS `title`, 
       `m`.`description`     AS `description`, 
       `m`.`source`          AS `source`, 
       `m`.`date`            AS `startdate`, 
       `m`.`date`            AS `enddate`, 
       `c`.`class`           AS `class`, 
       `c`.`classname`       AS `classname`, 
       `per`.`firstname`     AS `firstname`, 
       `per`.`lastname`      AS `lastname`, 
       `c`.`description`     AS `classdesc`, 
       `p`.`programid`       AS `programid`, 
       If((`p`.`subprogramof` IS NOT NULL),`mp`.`programname`, 
          `p`.`programname`) AS `programname`, 
       'Recorded'            AS `longname` 
FROM   ((((((((`media` `m` 
               JOIN `mediaaudience` `ma` 
                 ON (((`m`.`mediaid` = `ma`.`mediaid`) 
                      AND (`ma`.`audiencetype` = 'Public') 
                      AND ((`ma`.`enddate` < Now()) 
                            OR Isnull(`ma`.`enddate`))))) 
              LEFT JOIN `mediapresenter` `mpp` 
                ON ((`m`.`mediaid` = `mpp`.`mediaid`))) 
             LEFT JOIN `person` `per` 
               ON ((`mpp`.`personid` = `per`.`personid`))) 
            LEFT JOIN `mediaaudience` `mad` 
              ON (((`ma`.`mediaid` = `mad`.`mediaid`) 
                   AND (`mad`.`audiencetype` = 'classid')))) 
           LEFT JOIN `class` `c` 
             ON ((`mad`.`audienceid` = `c`.`classid`))) 
          LEFT JOIN `program_class` `pc` 
            ON ((`c`.`classid` = `pc`.`classid`))) 
         LEFT JOIN `program` `p` 
           ON ((`pc`.`programid` = `p`.`programid`))) 
        LEFT JOIN `program` `mp` 
          ON ((`p`.`subprogramof` = `mp`.`programid`))) 
UNION 
SELECT 'sectionid'           AS `idtype`, 
       `cc`.`sectionid`      AS `id`, 
       `cc`.`title`          AS `title`, 
       `cc`.`description`    AS `description`, 
       `l`.`mapurl`          AS `mapurl`, 
       `cc`.`starttime`      AS `startdate`, 
       `cc`.`endtime`        AS `enddate`, 
       `c`.`class`           AS `class`, 
       `c`.`classname`       AS `classname`, 
       `per`.`firstname`     AS `firstname`, 
       `per`.`lastname`      AS `lastname`, 
       `c`.`description`     AS `classdesc`, 
       `p`.`programid`       AS `programid`, 
       If((`p`.`subprogramof` IS NOT NULL),`mp`.`programname`, 
          `p`.`programname`) AS `programname`, 
       `d`.`longname`        AS `longname` 
FROM   ((((((((`calendarcache` `cc` 
               JOIN `section` `s` 
                 ON ((`cc`.`sectionid` = `s`.`sectionid`))) 
              LEFT JOIN `person` `per` 
                ON ((`s`.`teacherid` = `per`.`personid`))) 
             LEFT JOIN `division` `d` 
               ON ((`s`.`divisionid` = `d`.`divisionid`))) 
            LEFT JOIN `location` `l` 
              ON ((`s`.`locationid` = `l`.`locationid`))) 
           LEFT JOIN `class` `c` 
             ON ((`s`.`classid` = `c`.`classid`))) 
          LEFT JOIN `program_class` `pc` 
            ON ((`c`.`classid` = `pc`.`classid`))) 
         LEFT JOIN `program` `p` 
           ON ((`pc`.`programid` = `p`.`programid`))) 
        LEFT JOIN `program` `mp` 
          ON ((`p`.`subprogramof` = `mp`.`programid`))) 
WHERE  (NOT ((`cc`.`description` LIKE '%{cs}%')))

if x <> 1 then null; end if;
3.47 KB in 7 ms with coderay