SQL Abfragen nochmal sortieren

DenisCGN

Mitglied
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
 
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:
SQL:
(
    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.
 
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 ?
 
Zuletzt bearbeitet:
Übereinstimmungen in den Feldern war in etwa so gemeint:
Code:
 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:
SQL:
(
  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:
 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 [code=sql][/code] 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:
SQL:
(
    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 [phpf]strftime[/phpf] formatieren kannst - Find ich zumindest für PHP netter und flexibler.
 
Zuletzt bearbeitet:
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
 
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
, 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.
SQL:
SELECT * FROM tbl_test
Dies macht man hatl mit den beiden oben genannten Tags.
 
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"

SQL:
"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
 
Zuletzt bearbeitet:
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

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:
SQL:
(
    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:
`mhq_cms_previev`.`event` AS `genre
Und schon sind auch Spalten-, Alias- und Tabellenbezeichnungen sauber und sicher.

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:
"(
    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
;"
 
Zurück