tutorials.de-Buchverschenkaktion 08/2010
+ Auf Thema antworten
  1. 26.12.07 11:35 #1
    DenisCGN DenisCGN ist offline Mitglied Bronze DenisCGN hat eine blütenweiße Weste
    Registriert seit
    Apr 2004
    Beiträge
    44
    Frohe Weihnachten ihr lieben,

    ich arbeite mit DreamweaverMX. Dort haben ich 3 Datenbankabfragen erstellt. Nun möchte ich aus diesen 3 unterschiedlichen Abfragen (NEWS, CDS, KONZERTE) eine RSS FEED erstellen. Soll heißen ich möchte diese 3 unterschiedlichen Abfragen nochmals untereinander nach dem Einstelldatum sortieren. Mit Dreamweaer geht das irgendwie nicht. Ich muß rein theoretisch alle 3 Abfragen speichern und dann nochmals mit PHP oder so sortieren, also vielleicht eine ganz neue Abfrage erstellen.

    Kann mir da jemand helfen ?

    Grüße
    der Denis
    Zitieren Zitieren
  2. 26.12.07 11:47 #2
    Benutzerbild von maeTimmae
    maeTimmae maeTimmae ist offline Mitglied Platin maeTimmae ist jedem bekannt maeTimmae ist jedem bekannt maeTimmae ist jedem bekannt maeTimmae ist jedem bekannt
    Registriert seit
    Aug 2007
    Ort
    Erfurt, Saarbrücken, Leipzig und Fulda
    Beiträge
    515
    Solltest du die Anfragen uniformieren können, sprich die zu erwartenden Ergebnisse in ein gemeinsames Format bringen können, ließe sich mit UNION und einer anschließenden Sortierung arbeiten:
    Code sql:
    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

    (
    SELECT
        `news_id` AS `id`,
        `news_title` AS `title`,
        `news_dateline` AS `dateline`
    FROM
        `news`
) UNION (
    SELECT
        `konzert_id` AS `id`,
        `konzert_title` AS `title`,
        `konzert_dateline` AS `dateline`
    FROM
        `konzerte`
) UNION (
    SELECT
        `cd_id` AS `id`,
        `cd_title` AS `title`,
        `cd_dateline` AS `dateline`
    FROM
        `cds`
)
ORDER BY
    `dateline` DESC
LIMIT
    10;

    Das erste Select gibt Spaltenanzahl und Bezeichnungen vor, weshalb die "AS" in den beiden anderen überflüssig sind - Sind hier nur zur Orientierung gedacht.
    Ein wenig optimieren kann man das dann auch noch, wie zB jeweilige Sortierung und Limitierungen auch noch in den einzelnen Selects, wodurch nicht mehr alle Ergebnisse ausgelagert werden müssen, könnte aber auch sein, dass das der MySQL Optimizer automatisch macht.
    Zitieren Zitieren
  3. 28.12.07 15:07 #3
    DenisCGN DenisCGN ist offline Mitglied Bronze DenisCGN hat eine blütenweiße Weste
    Registriert seit
    Apr 2004
    Beiträge
    44
    Hallo mae Timmae,

    das werde ich mal versuchen. Ich habe allerdings nur ein übereinstimmendes Feld das Datumsfeld ad_date. Alle Selectabfragen werden durch unterschiedliche Beziehungen erstellt. Haben daher nur den Künstler bzw. das Ad_date gemeinsam.
    Ich werde es mal versuchen !

    So ist mein versuch...

    SELECT mhq_cms_konzertfotos.id, DATE_FORMAT(mhq_cms_konzertfotos.datum, '%d.%m.%Y') AS datum, mhq_cms_artist_event.name, mhq_cms_venues_locations.name AS venue, mhq_cms_venues_locations.ort
    FROM mhq_cms_konzertfotos, mhq_cms_artist_event, mhq_cms_venues_locations
    WHERE mhq_cms_konzertfotos.artist_event = mhq_cms_artist_event.id AND mhq_cms_konzertfotos.venue_location = mhq_cms_venues_locations.id UNION SELECT mhq_cms_cd_reviews.id, mhq_cms_cd_reviews.titel, LEFT(mhq_cms_cd_reviews.review, 250) AS review, mhq_cms_artist_event.name FROM mhq_cms_cd_reviews, mhq_cms_artist_event WHERE mhq_cms_cd_reviews.artist_event = mhq_cms_artist_event.id UNION SELECT mhq_cms_dvd_reviews.id, mhq_cms_dvd_reviews.titel, LEFT(mhq_cms_dvd_reviews.review, 250) AS review, mhq_cms_artist_event.name FROM mhq_cms_dvd_reviews, mhq_cms_artist_event WHERE mhq_cms_dvd_reviews.artist_event = mhq_cms_artist_event.id UNION SELECT mhq_cms_konzertberichte.id, DATE_FORMAT(mhq_cms_konzertberichte.datum, '%d.%m.%Y') AS datum, LEFT(mhq_cms_konzertberichte.review, 250) AS review, mhq_cms_artist_event.name, mhq_cms_venues_locations.ort, mhq_cms_venues_locations.name AS venue FROM mhq_cms_konzertberichte, mhq_cms_artist_event, mhq_cms_venues_locations WHERE mhq_cms_konzertberichte.artist_event = mhq_cms_artist_event.id AND mhq_cms_konzertberichte.venue_location = mhq_cms_venues_locations.id UNION SELECT mhq_cms_buch_reviews.id, mhq_cms_buch_reviews.titel, LEFT(mhq_cms_buch_reviews.review, 250) AS review, mhq_cms_buch_reviews.autor FROM mhq_cms_buch_reviews
    ORDER BY ad_date DESC LIMIT 10

    fehlermeldung ist hier, SELECT have diffrent coloms ?
    Geändert von DenisCGN (28.12.07 um 19:42 Uhr)
    Zitieren Zitieren
  4. 28.12.07 19:46 #4
    Benutzerbild von maeTimmae
    maeTimmae maeTimmae ist offline Mitglied Platin maeTimmae ist jedem bekannt maeTimmae ist jedem bekannt maeTimmae ist jedem bekannt maeTimmae ist jedem bekannt
    Registriert seit
    Aug 2007
    Ort
    Erfurt, Saarbrücken, Leipzig und Fulda
    Beiträge
    515
    Übereinstimmungen in den Feldern war in etwa so gemeint:
    Code :
    1
2
3
4
5
6
7
8
9
10
11

     concert_id | artist | ad_date | concert_title | concert_description 
------------+--------+---------+---------------+---------------------
     ...    |   ...  |   ...   |      ...      |          ...        
 
 news_id | news_title | news_text | ad_date | artist 
---------+------------+-----------+---------+--------
   ...   |     ...    |    ...    |   ...   |   ...  
 
cd_id | cd_title | ad_date | artist 
------+----------+---------+--------
  ... |    ...   |   ...   |   ...

    Abfrage:
    Code sql:
    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

    (
  SELECT
      `news_id` AS `id`,
      `news_title` AS `title`,
      `news_text` AS `text`,
      `ad_date` AS `dateline`,
      `artist` AS `artist`
  FROM
      `news`
) UNION (
  SELECT
      `concert_id` AS `id`,
      `concert_title` AS `title`,
      `concert_description` AS `text`,
      `ad_date` AS `dateline`,
      `artist` AS `artist`
  FROM
      `konzerte`
) UNION (
  SELECT
      `cd_id` AS `id`,
      `cd_title` AS `title`,
       NULL AS `text`,
      `ad_date` AS `dateline`,
      `artist` AS `artist`
  FROM
      `cds`
)
ORDER BY
  `dateline` DESC
LIMIT
  10;
    Ergibt Tabelle diesen Formats:
    Code :
    1
2
3

     id | title | text | dateline | artist 
----+-------+------+----------+--------
 .. |  ...  |  ... |    ...   |   ...

    Ich habe mal bewusst bei der CD-Tabelle eine Text-Spalte rausgelassen, damit du siehst, wie das mit der "Gemeinsamkeit" gemeint war. Letztenendes muss nur die Abfrage und die Anordnung der Ergebnisspalten in dieser ein wenig abstrahiert werden, dann klappt das alles ganz bequem.

    Edit: Da ist ja noch was dazugekommen Ich schaue mal schnell drüber, und du schaust dir bitte derweil mein Beispiel an.
    Übrigens kannst du [sql][/sql] benutzen, um den Querystring nach SQL-Syntax highlighten zu lassen. Das sieht nicht nur schöner aus, sondern macht es auch lesbarer

    Keine Garantie auf Richtigkeit:
    Code sql:
    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
78
79
80
81
82
83
84

    (
    SELECT
        `mhq_cms_konzertfotos`.`id` AS `id`,
         NULL AS `titel`,
         NULL AS `review`,
         UNIX_TIMESTAMP(`mhq_cms_konzertfotos`.`datum`) AS `datum`,
         NULL AS `autor`,
        `mhq_cms_artist_event`.`name` AS `name`,
        `mhq_cms_venues_locations`.`name` AS `venue`,
        `mhq_cms_venues_locations`.`ort` AS `ort`
    FROM
        `mhq_cms_artist_event`
    LEFT JOIN
        `mhq_cms_konzertfotos` ON
        `mhq_cms_konzertfotos`.`artist_event` = `mhq_cms_artist_event`.`id`
    LEFT JOIN
        `mhq_cms_venues_locations` ON
        `mhq_cms_venues_locations`.`id` = `mhq_cms_konzertfotos`.`venue_location`
UNION (
    SELECT
        `mhq_cms_cd_reviews`.`id` AS `id`,
        `mhq_cms_cd_reviews.titel` AS `titel`,
         LEFT(`mhq_cms_cd_reviews`.`review`, 250) AS `review`,
         NULL AS `autor`,
         0 AS `datum`,
        `mhq_cms_artist_event`.`name` AS `name`,
         NULL AS `venue`,
         NULL AS `ort`
    FROM
        `mhq_cms_cd_reviews`
    LEFT JOIN
        `mhq_cms_artist_event` ON
        `mhq_cms_artist_event`.`id` = `mhq_cms_cd_reviews`.`artist_event`
) UNION (
    SELECT
        `mhq_cms_dvd_reviews`.`id` AS `id`,
        `mhq_cms_dvd_reviews`.`titel` AS `titel`,
         LEFT(`mhq_cms_dvd_reviews`.`review`, 250) AS `review`,
         NULL AS `autor`,
         0 AS `datum`,
        `mhq_cms_artist_event`.`name` AS `name`,
         NULL AS `venue`,
         NULL AS `ort`
    FROM
        `mhq_cms_dvd_reviews
    LEFT JOIN
        `mhq_cms_artist_event` ON
        `mhq_cms_artist_event`.`id` = `mhq_cms_dvd_reviews`.`artist_event`
) UNION (
    SELECT
        `mhq_cms_konzertberichte`.`id` AS `id`,
         NULL AS `titel`,
         LEFT(`mhq_cms_konzertberichte`.`review`, 250) AS `review`,
         NULL AS `autor`,
         UNIX_TIMESTAMP(`mhq_cms_konzertberichte`.`datum`) AS `datum`,
        `mhq_cms_artist_event`.`name` AS `name`,
        `mhq_cms_venues_locations`.`name` AS `venue`,
        `mhq_cms_venues_locations`.`ort` AS `ort`
    FROM
        `mhq_cms_konzertberichte`
    LEFT JOIN
        `mhq_cms_artist_event` ON
        `mhq_cms_artist_event`.`id` = `mhq_cms_konzertberichte`.`artist_event`
    LEFT JOIN
        `mhq_cms_venues_locations` ON
        `mhq_cms_venues_locations`.`id` = `mhq_cms_konzertberichte`.`venue_location`
) UNION (
    SELECT
        `mhq_cms_buch_reviews`.`id` AS `id`,
        `mhq_cms_buch_reviews`.`titel` AS `titel`,
         LEFT(`mhq_cms_buch_reviews`.`review`, 250) AS `review`,
        `mhq_cms_buch_reviews`.`autor` AS `autor`,
         0 AS `datum`,
         NULL AS `name`,
         NULL AS `venue`,
         NULL AS `ort`
    FROM
        `mhq_cms_buch_reviews`
)
ORDER BY
    `datum` DESC
LIMIT
    10
;
    Die Joins sind wahrscheinlich nicht so gut, aber dazu fehlt mir zur Zeit der Strukturdurchblick ein bisschen. Prinzip wie gehabt. Habe mal die Datumssachen auf Unixtime gestimmt, damit die Sortierung schneller abläuft und du in PHP das Datum via zB strftime() formatieren kannst - Find ich zumindest für PHP netter und flexibler.
    Geändert von maeTimmae (28.12.07 um 20:17 Uhr)
    Zitieren Zitieren
  5. 30.12.07 08:50 #5
    DenisCGN DenisCGN ist offline Mitglied Bronze DenisCGN hat eine blütenweiße Weste
    Registriert seit
    Apr 2004
    Beiträge
    44
    Moin moin,

    dh, die Anzahl der Spalten muss gleich sein und auch deren Bezeichnung ? Fehlende Spalten ergänze ich mit NULL AS fehlender SPALTENNAME. Dh, ich sollte vorher alle Tabellen vergleichen und auswählen was ich wirklich von allen brauche. Eine Schnittmenge
    Für mich ärgerlich, da ich das mit Dreamweaver MX erstelle und das SQL-Feld viel zu klein ist.

    Außerdem vielen Dank für die Erklärung von LEFT JOIN. Das ist also der Ersatz für WHERE ? Die Abfrage ob ACTION = 1 ist, geht das auch über JOIN ?

    So jetzt versuch ich das mal.

    Danke
    der Denis

    ps. was meinst Du mit SQL/SQL
    Zitieren Zitieren
  6. 02.01.08 12:29 #6
    Benutzerbild von ronaldo84
    ronaldo84 ronaldo84 ist offline Mitglied Brokat ronaldo84 wird schon bald berühmt werden
    Registriert seit
    Dec 2004
    Ort
    Berlin / Rudow
    Beiträge
    452
    Ja, du kannst auch noch eine Where Klausel unter die Abfrage, dabei mußt du nur beachten, das wenn Spaltennamen in mehrere Tabellen vorkommen mußt du den Tabellennamen bzw. den Alias vor den Spaltennamen stellen, damit er weiß welche Spalte er nehmen soll.
    maeTimmae meint mit dem
    sql /sql
    , dass dieses Forum die Möglichkeit hat, den SQL-Code als SQL-Code zu kennzeichnen. Damit die Syntax hervorgehoben wird, rein zur Übersicht.
    Z.B.
    Code sql:
    1

    SELECT * FROM tbl_test
    Dies macht man hatl mit den beiden oben genannten Tags.
    Bitte alle erledigten Themen abschliessen.
    Zitieren Zitieren
  7. 02.01.08 14:34 #7
    DenisCGN DenisCGN ist offline Mitglied Bronze DenisCGN hat eine blütenweiße Weste
    Registriert seit
    Apr 2004
    Beiträge
    44
    Hat sich erledigt ich hatte vergessen die SELECT Abfragen in () zu setzten !


    Danke für eure Hilfe !
    der Denis

    aber vielleicht kann mir dennoch jemand sagen wie ich den Code übersichtlicher schreiben kann



    Hallo,

    ich bekomme es nicht wirklich hin...die Fehlermeldung ist "incorrect usage of UNION and ORDER BY"

    Code sql:
    1

    "SELECT NULL AS genre, mhq_cms_buch_reviews.id AS id, mhq_cms_buch_reviews.autor AS act, mhq_cms_buch_reviews.titel AS titel, LEFT(mhq_cms_buch_reviews.review, 200) AS content, DATE_FORMAT(mhq_cms_buch_reviews.ad_date, '%d.%m.%Y') AS eingestellt, DATE_FORMAT(mhq_cms_buch_reviews.release_datum, '%d.%m.%Y') AS datum, 'Buch' AS kategorie FROM mhq_cms_buch_reviews WHERE mhq_cms_buch_reviews.`action` = '1' ORDER BY mhq_cms_buch_reviews.ad_date DESC LIMIT 20 UNION SELECT NULL AS genre, mhq_cms_cd_reviews.id AS id, mhq_cms_artist_event.name AS act, mhq_cms_cd_reviews.titel AS titel, LEFT(mhq_cms_cd_reviews.review, 200) AS content, DATE_FORMAT(mhq_cms_cd_reviews.ad_date, '%d.%m.%Y') AS eingestellt, DATE_FORMAT(mhq_cms_cd_reviews.release_datum, '%d.%m.%Y') AS datum, 'CD' AS kategorie FROM mhq_cms_cd_reviews, mhq_cms_artist_event WHERE mhq_cms_cd_reviews.artist_event = mhq_cms_artist_event.id AND mhq_cms_cd_reviews.`action` = '1' ORDER BY mhq_cms_cd_reviews.ad_date DESC LIMIT 20 UNION SELECT NULL AS genre, mhq_cms_dvd_reviews.id AS id, mhq_cms_artist_event.name AS act, mhq_cms_dvd_reviews.titel AS titel, LEFT(mhq_cms_dvd_reviews.review, 200) AS content, DATE_FORMAT(mhq_cms_dvd_reviews.ad_date, '%d.%m.%Y') AS eingestellt, DATE_FORMAT(mhq_cms_dvd_reviews.release_datum, '%d.%m.%Y') AS datum, 'DVD' AS kategorie FROM mhq_cms_dvd_reviews, mhq_cms_artist_event WHERE mhq_cms_dvd_reviews.artist_event = mhq_cms_artist_event.id AND mhq_cms_dvd_reviews.`action` = '1' ORDER BY mhq_cms_dvd_reviews.ad_date DESC LIMIT 20 UNION SELECT NULL AS genre, mhq_cms_konzertberichte.id AS id, mhq_cms_artist_event.name AS act, mhq_cms_konzertberichte.titel AS titel, LEFT(mhq_cms_konzertberichte.review, 200) AS content, DATE_FORMAT(mhq_cms_konzertberichte.ad_date, '%d.%m.%Y') AS engestellt, DATE_FORMAT(mhq_cms_konzertberichte.datum, '%d.%m.%Y') AS datum, 'Bericht' AS kategorie FROM mhq_cms_konzertberichte, mhq_cms_artist_event WHERE mhq_cms_konzertberichte.artist_event = mhq_cms_artist_event.id AND mhq_cms_konzertberichte.`action` = '1' ORDER BY mhq_cms_konzertberichte.ad_date DESC LIMIT 20 UNION SELECT NULL AS genre, mhq_cms_konzertfotos.id AS id, mhq_cms_artist_event.name AS act, mhq_cms_konzertfotos.titel AS titel, 'Konzertfotos / Festivalfotos' AS content, DATE_FORMAT(mhq_cms_konzertfotos.ad_date, '%d.%m.%Y') AS eingestellt, DATE_FORMAT(mhq_cms_konzertfotos.datum, '%d.%m.%Y') AS datum, 'Fotos' AS kategorie FROM mhq_cms_konzertfotos, mhq_cms_artist_event WHERE mhq_cms_konzertfotos.artist_event = mhq_cms_artist_event.id AND mhq_cms_konzertfotos.`action` = '1' ORDER BY mhq_cms_konzertfotos.ad_date DESC LIMIT 20 UNION SELECT NULL AS genre, mhq_cms_news.id AS id, mhq_cms_artist_event.name AS act, mhq_cms_news.titel AS titel, LEFT(mhq_cms_news.news, 200) AS content, DATE_FORMAT(mhq_cms_news.ad_date, '%d.%m.%Y') AS eingestellt, DATE_FORMAT(mhq_cms_news.von_datum, '%d.%m.%Y') AS datum, 'News' AS kategorie FROM mhq_cms_news, mhq_cms_artist_event WHERE mhq_cms_news.artist_event = mhq_cms_artist_event.id AND mhq_cms_news.`action` = '1' AND CURRENT_DATE BETWEEN mhq_cms_news.von_datum AND mhq_cms_news.bis_datum ORDER BY mhq_cms_news.von_datum DESC LIMIT 20 UNION SELECT NULL AS genre, mhq_cms_interviews.id AS id, mhq_cms_artist_event.name AS act, mhq_cms_interviews.titel AS titel, LEFT(mhq_cms_interviews.interview, 200) AS content, DATE_FORMAT(mhq_cms_interviews.ad_date, '%d.%m.%Y') AS eingestellt, DATE_FORMAT(mhq_cms_interviews.datum, '%d.%m.%Y') AS datum, 'Interview' AS kategorie FROM mhq_cms_interviews, mhq_cms_artist_event WHERE mhq_cms_interviews.artist_event = mhq_cms_artist_event.id AND mhq_cms_interviews.`action` = '1' ORDER BY mhq_cms_interviews.ad_date DESC LIMIT 20 UNION SELECT mhq_cms_preview.event AS genre, mhq_cms_preview.id AS id, mhq_cms_artist_event.name AS act, mhq_cms_preview.titel AS titel, LEFT(mhq_cms_preview.text, 200) AS content, DATE_FORMAT(mhq_cms_preview.ad_date, '%d.%m.%Y') AS eingestellt, DATE_FORMAT(mhq_cms_preview.datum, '%d.%m.%Y') AS datum, 'Preview' AS kategorie FROM mhq_cms_preview, mhq_cms_artist_event WHERE mhq_cms_preview.artist_event = mhq_cms_artist_event.id AND mhq_cms_preview.`action` = '1' AND CURRENT_DATE <= mhq_cms_preview.datum ORDER BY mhq_cms_preview.ad_date DESC LIMIT 20 ORDER BY einstellen DESC LIMIT 10";

    Ausserdem wüßte ich gerne, wie ich den Quellcode etwas übersichtlicher schreiben kann.

    in DW müssen die SELECT Abfragen immer zwischen "" stehen.

    Grüße
    Denis
    Geändert von DenisCGN (02.01.08 um 14:50 Uhr) Grund: Fehler selbst gefunden ;-)
    Zitieren Zitieren
  8. 03.01.08 17:21 #8
    Benutzerbild von maeTimmae
    maeTimmae maeTimmae ist offline Mitglied Platin maeTimmae ist jedem bekannt maeTimmae ist jedem bekannt maeTimmae ist jedem bekannt maeTimmae ist jedem bekannt
    Registriert seit
    Aug 2007
    Ort
    Erfurt, Saarbrücken, Leipzig und Fulda
    Beiträge
    515
    Zitat Zitat von DenisCGN Beitrag anzeigen
    ich bekomme es nicht wirklich hin...die Fehlermeldung ist "incorrect usage of UNION and ORDER BY"
    Die Lösung hast du ja nun schon selbst herausgefunden - Google spuckt dazu zB das hier aus: Lösungsvorschlag @ DevChunks.com

    Zitat Zitat von DenisCGN Beitrag anzeigen
    vielleicht kann mir dennoch jemand sagen wie ich den Code übersichtlicher schreiben kann
    Persönlich bevorzuge ich die für mich logische Einrückung, wie hier zu sehen:
    Code sql:
    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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164

    (
    SELECT
        NULL AS genre,
        mhq_cms_buch_reviews.id AS id,
        mhq_cms_buch_reviews.autor AS act,
        mhq_cms_buch_reviews.titel AS titel,
        LEFT(mhq_cms_buch_reviews.review, 200) AS content,
        DATE_FORMAT(mhq_cms_buch_reviews.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_buch_reviews.release_datum, '%d.%m.%Y') AS datum,
        'Buch' AS kategorie
    FROM
        mhq_cms_buch_reviews
    WHERE
        mhq_cms_buch_reviews.`action` = '1'
    ORDER BY
        mhq_cms_buch_reviews.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_cd_reviews.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_cd_reviews.titel AS titel,
        LEFT(mhq_cms_cd_reviews.review, 200) AS content,
        DATE_FORMAT(mhq_cms_cd_reviews.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_cd_reviews.release_datum, '%d.%m.%Y') AS datum,
        'CD' AS kategorie
    FROM
        mhq_cms_cd_reviews,
        mhq_cms_artist_event
    WHERE
        mhq_cms_cd_reviews.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_cd_reviews.`action` = '1'
    ORDER BY
        mhq_cms_cd_reviews.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_dvd_reviews.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_dvd_reviews.titel AS titel,
        LEFT(mhq_cms_dvd_reviews.review, 200) AS content,
        DATE_FORMAT(mhq_cms_dvd_reviews.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_dvd_reviews.release_datum, '%d.%m.%Y') AS datum,
        'DVD' AS kategorie
    FROM
        mhq_cms_dvd_reviews,
        mhq_cms_artist_event
    WHERE
        mhq_cms_dvd_reviews.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_dvd_reviews.`action` = '1'
    ORDER BY
        mhq_cms_dvd_reviews.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_konzertberichte.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_konzertberichte.titel AS titel,
        LEFT(mhq_cms_konzertberichte.review, 200) AS content,
        DATE_FORMAT(mhq_cms_konzertberichte.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_konzertberichte.datum, '%d.%m.%Y') AS datum,
        'Bericht' AS kategorie
    FROM
        mhq_cms_konzertberichte,
        mhq_cms_artist_event
    WHERE
        mhq_cms_konzertberichte.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_konzertberichte.`action` = '1'
    ORDER BY
        mhq_cms_konzertberichte.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_konzertfotos.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_konzertfotos.titel AS titel,
        'Konzertfotos / Festivalfotos' AS content,
        DATE_FORMAT(mhq_cms_konzertfotos.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_konzertfotos.datum, '%d.%m.%Y') AS datum,
        'Fotos' AS kategorie
    FROM
        mhq_cms_konzertfotos,
        mhq_cms_artist_event
    WHERE
        mhq_cms_konzertfotos.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_konzertfotos.`action` = '1'
    ORDER BY
        mhq_cms_konzertfotos.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_news.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_news.titel AS titel, LEFT(mhq_cms_news.news, 200) AS content,
        DATE_FORMAT(mhq_cms_news.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_news.von_datum, '%d.%m.%Y') AS datum,
        'News' AS kategorie FROM mhq_cms_news, mhq_cms_artist_event
    WHERE
        mhq_cms_news.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_news.`action` = '1' AND
        CURRENT_DATE BETWEEN
            mhq_cms_news.von_datum AND
            mhq_cms_news.bis_datum
    ORDER BY
        mhq_cms_news.von_datum DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_interviews.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_interviews.titel AS titel,
        LEFT(mhq_cms_interviews.interview, 200) AS content,
        DATE_FORMAT(mhq_cms_interviews.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_interviews.datum, '%d.%m.%Y') AS datum,
        'Interview' AS kategorie
    FROM
        mhq_cms_interviews,
        mhq_cms_artist_event
    WHERE
        mhq_cms_interviews.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_interviews.`action` = '1'
    ORDER BY
        mhq_cms_interviews.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        mhq_cms_preview.event AS genre,
        mhq_cms_preview.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_preview.titel AS titel,
        LEFT(mhq_cms_preview.text, 200) AS content,
        DATE_FORMAT(mhq_cms_preview.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_preview.datum, '%d.%m.%Y') AS datum,
        'Preview' AS kategorie
    FROM
        mhq_cms_preview,
        mhq_cms_artist_event
    WHERE
        mhq_cms_preview.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_preview.`action` = '1' AND
        CURRENT_DATE <= mhq_cms_preview.datum
    ORDER BY
        mhq_cms_preview.ad_date DESC
    LIMIT
        20
)
ORDER BY
    einstellen DESC
LIMIT
    10
;
    Ist sicherlich Geschmackssache, aber man sieht sofort, was wo hingehört. Dabei fällt auch ins Auge, wie die einzelnen Selects aussehen, und wohin die Klauseln gehören. Zeilenumbrüche und Einrückungen sind in MySQL kein Verbrechen.
    Eventuell solltest du dir auch für die Zukunft angewöhnen, Bezeichner zwischen Backticks zu stellen, wie du es inkonsequent schon ab und zu gemacht hast:
    Code :
    1

    `mhq_cms_previev`.`event` AS `genre
    Und schon sind auch Spalten-, Alias- und Tabellenbezeichnungen sauber und sicher.

    Zitat Zitat von DenisCGN Beitrag anzeigen
    in DW müssen die SELECT Abfragen immer zwischen "" stehen.
    Dann pack den String einfach noch in Anführungszeichen. Einrücken und umbrechen sollte wohl nichtsdestotrotz auch mit DreamWeaver gehen

    Code :
    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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164

    "(
    SELECT
        NULL AS genre,
        mhq_cms_buch_reviews.id AS id,
        mhq_cms_buch_reviews.autor AS act,
        mhq_cms_buch_reviews.titel AS titel,
        LEFT(mhq_cms_buch_reviews.review, 200) AS content,
        DATE_FORMAT(mhq_cms_buch_reviews.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_buch_reviews.release_datum, '%d.%m.%Y') AS datum,
        'Buch' AS kategorie
    FROM
        mhq_cms_buch_reviews
    WHERE
        mhq_cms_buch_reviews.`action` = '1'
    ORDER BY
        mhq_cms_buch_reviews.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_cd_reviews.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_cd_reviews.titel AS titel,
        LEFT(mhq_cms_cd_reviews.review, 200) AS content,
        DATE_FORMAT(mhq_cms_cd_reviews.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_cd_reviews.release_datum, '%d.%m.%Y') AS datum,
        'CD' AS kategorie
    FROM
        mhq_cms_cd_reviews,
        mhq_cms_artist_event
    WHERE
        mhq_cms_cd_reviews.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_cd_reviews.`action` = '1'
    ORDER BY
        mhq_cms_cd_reviews.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_dvd_reviews.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_dvd_reviews.titel AS titel,
        LEFT(mhq_cms_dvd_reviews.review, 200) AS content,
        DATE_FORMAT(mhq_cms_dvd_reviews.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_dvd_reviews.release_datum, '%d.%m.%Y') AS datum,
        'DVD' AS kategorie
    FROM
        mhq_cms_dvd_reviews,
        mhq_cms_artist_event
    WHERE
        mhq_cms_dvd_reviews.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_dvd_reviews.`action` = '1'
    ORDER BY
        mhq_cms_dvd_reviews.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_konzertberichte.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_konzertberichte.titel AS titel,
        LEFT(mhq_cms_konzertberichte.review, 200) AS content,
        DATE_FORMAT(mhq_cms_konzertberichte.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_konzertberichte.datum, '%d.%m.%Y') AS datum,
        'Bericht' AS kategorie
    FROM
        mhq_cms_konzertberichte,
        mhq_cms_artist_event
    WHERE
        mhq_cms_konzertberichte.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_konzertberichte.`action` = '1'
    ORDER BY
        mhq_cms_konzertberichte.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_konzertfotos.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_konzertfotos.titel AS titel,
        'Konzertfotos / Festivalfotos' AS content,
        DATE_FORMAT(mhq_cms_konzertfotos.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_konzertfotos.datum, '%d.%m.%Y') AS datum,
        'Fotos' AS kategorie
    FROM
        mhq_cms_konzertfotos,
        mhq_cms_artist_event
    WHERE
        mhq_cms_konzertfotos.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_konzertfotos.`action` = '1'
    ORDER BY
        mhq_cms_konzertfotos.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_news.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_news.titel AS titel, LEFT(mhq_cms_news.news, 200) AS content,
        DATE_FORMAT(mhq_cms_news.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_news.von_datum, '%d.%m.%Y') AS datum,
        'News' AS kategorie FROM mhq_cms_news, mhq_cms_artist_event
    WHERE
        mhq_cms_news.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_news.`action` = '1' AND
        CURRENT_DATE BETWEEN
            mhq_cms_news.von_datum AND
            mhq_cms_news.bis_datum
    ORDER BY
        mhq_cms_news.von_datum DESC
    LIMIT
        20
) UNION (
    SELECT
        NULL AS genre,
        mhq_cms_interviews.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_interviews.titel AS titel,
        LEFT(mhq_cms_interviews.interview, 200) AS content,
        DATE_FORMAT(mhq_cms_interviews.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_interviews.datum, '%d.%m.%Y') AS datum,
        'Interview' AS kategorie
    FROM
        mhq_cms_interviews,
        mhq_cms_artist_event
    WHERE
        mhq_cms_interviews.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_interviews.`action` = '1'
    ORDER BY
        mhq_cms_interviews.ad_date DESC
    LIMIT
        20
) UNION (
    SELECT
        mhq_cms_preview.event AS genre,
        mhq_cms_preview.id AS id,
        mhq_cms_artist_event.name AS act,
        mhq_cms_preview.titel AS titel,
        LEFT(mhq_cms_preview.text, 200) AS content,
        DATE_FORMAT(mhq_cms_preview.ad_date, '%d.%m.%Y') AS eingestellt,
        DATE_FORMAT(mhq_cms_preview.datum, '%d.%m.%Y') AS datum,
        'Preview' AS kategorie
    FROM
        mhq_cms_preview,
        mhq_cms_artist_event
    WHERE
        mhq_cms_preview.artist_event = mhq_cms_artist_event.id AND
        mhq_cms_preview.`action` = '1' AND
        CURRENT_DATE <= mhq_cms_preview.datum
    ORDER BY
        mhq_cms_preview.ad_date DESC
    LIMIT
        20
)
ORDER BY
    einstellen DESC
LIMIT
    10
;"
    Zitieren Zitieren
« Oracle 10g XE auf Ubuntu 7.10 will nicht | MySQL: Wie lassen sich Kategorien zählen? (Count()?) »

Ähnliche Themen

  1. [MySQL] Zwei Tabellen abfragen und Ergebnis sortieren
    Von DJLopez im Forum Relationale Datenbanksysteme
    Antworten: 3
    Letzter Beitrag: 01.07.07, 23:49
  2. SQL-Abfragen
    Von Atasch im Forum Relationale Datenbanksysteme
    Antworten: 2
    Letzter Beitrag: 20.01.07, 15:11
  3. SQL Abfragen
    Von Hattrix im Forum Relationale Datenbanksysteme
    Antworten: 2
    Letzter Beitrag: 15.11.06, 13:56
  4. Inhalt aus 2 SQL-Tabellen abfragen und sortieren
    Von salonhuber im Forum PHP
    Antworten: 6
    Letzter Beitrag: 08.05.06, 09:08
  5. nochmal ein Login bereich, ohne SQL
    Von asmodis! im Forum PHP
    Antworten: 1
    Letzter Beitrag: 28.03.02, 10:15

Lesezeichen

Lesezeichen