76 lines
1.9 KiB
SQL
76 lines
1.9 KiB
SQL
-- phpMyAdmin SQL Dump
|
|
-- version 4.5.1
|
|
-- http://www.phpmyadmin.net
|
|
--
|
|
-- Host: 127.0.0.1
|
|
-- Erstellungszeit: 06. Dez 2018 um 13:59
|
|
-- Server-Version: 10.1.19-MariaDB
|
|
-- PHP-Version: 7.0.13
|
|
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
|
|
SET time_zone = "+00:00";
|
|
|
|
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
|
/*!40101 SET NAMES utf8mb4 */;
|
|
|
|
--
|
|
-- Datenbank: leitgedanken
|
|
--
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur des Views lieder_synopse
|
|
--
|
|
ALTER TABLE `lieder` CHANGE `zusatz` `zusatz` VARCHAR(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;;
|
|
|
|
drop view lieder_synopse;
|
|
CREATE VIEW lieder_synopse
|
|
AS
|
|
select 'CB' AS art
|
|
,d.CBID AS id
|
|
,e.Nr AS Nr
|
|
,'' AS zusatz
|
|
,e.Titel AS Titel
|
|
,e.Titel_suche AS Titel_suche
|
|
,d.Text AS Text
|
|
,d.Text_suche AS Text_suche
|
|
,c.kategorie
|
|
,b.unterkategorie
|
|
from lieder_kategorie_zuord a, lieder_unterkategorie b, lieder_kategorie c, chorbuch_texte d, chorbuch e
|
|
where a.lukid = b.lukid
|
|
and b.lkid=c.lkid
|
|
and a.id=e.cbid
|
|
and e.cbid=d.cbid
|
|
and a.art='CB'
|
|
union
|
|
select 'GB' AS art
|
|
,d.GBID AS id
|
|
,e.Nr AS Nr
|
|
,zusatz AS zusatz
|
|
,e.Titel AS Titel
|
|
,e.Titel_suche AS Titel_suche
|
|
,d.Text AS Text
|
|
,d.Text_suche AS Text_suche
|
|
,c.kategorie
|
|
,b.unterkategorie
|
|
from lieder_kategorie_zuord a, lieder_unterkategorie b, lieder_kategorie c, lieder_texte d, lieder e
|
|
where a.lukid = b.lukid
|
|
and b.lkid=c.lkid
|
|
and a.id=e.gbid
|
|
and e.gbid=d.gbid
|
|
and a.art='GB';
|
|
|
|
--
|
|
-- VIEW lieder_synopse
|
|
-- Daten: keine
|
|
--
|
|
|
|
|
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|