[Erledigt] Sql Zufall Bild von Album

[Forumsgast]

Irgendwie dreh ich mich momentan im Kreis und komm nicht so auf das Gewünschte Ergebnis bei der Sql Bildung.
Ich hab 3 Tabellen. (album , album_picture,picture)

Nun will ich alle Alben ausgeben und dazu 1 zufälliges Foto. Soweit funktioniert das auch. Aber ich möchte das das Zufällige Foto aus dem Album ist und aus keinem anderen Album.

Nun mischt er die Fotos allerdings quer durch die Alben. Sollte es kein Foto in dem Alben geben soll er Null ausgeben. Irgendwas mit der Beziehungs zuweisung stimmt nicht.

Naja vielleich fallt euch dazu was ein.
Zusammengefasst:
Pro Album ein zufälliges Bild. Es sollen dabei alle Alben Namen mit dem Zufälligen bild aus dem Album ausgeben werden

Hier mal meine Sql ansätze dazu

SELECT
 (SELECT
   `picture`.`thumb_filename`
  FROM
   `album`
   LEFT JOIN `album_picture` ON `album_picture`.`album_id` = `album`.`id`
   LEFT JOIN `picture` ON `picture`.`id` = `album_picture`.`picture_id`
  ORDER BY RAND() LIMIT 1 ) AS `thumb_filename`,
  `album`.`id`,`album`.`album`
FROM
 `album`
LEFT JOIN `album_picture` ON `album_picture`.`album_id` = `album`.`id`
LEFT JOIN `picture` ON `picture`.`id` = `album_picture`.`picture_id`
GROUP BY `album`.`album`
ORDER BY `album`.`sort` ASC 

Durch den Zufall setzt er es nicht in verbindung mit der Zuweisung des Alben Fotos.
Die 3 Tabellen sehen so aus.

CREATE TABLE IF NOT EXISTS `album` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sort` tinyint(5) unsigned NOT NULL DEFAULT '0',
  `album` varchar(25) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `album_picture`
--
CREATE TABLE IF NOT EXISTS `album_picture` (
  `album_id` int(11) unsigned NOT NULL,
  `picture_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`album_id`,`picture_id`),
  KEY `picture_id` (`picture_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `picture`
--
CREATE TABLE IF NOT EXISTS `picture` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sort` tinyint(5) unsigned NOT NULL DEFAULT '50',
  `filename` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `filetype` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `filesize` int(10) unsigned NOT NULL,
  `thumb_filename` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `thumb_filetype` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `report` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- Constraints der exportierten Tabellen
--
--
-- Constraints der Tabelle `album_picture`
--
ALTER TABLE `album_picture`
  ADD CONSTRAINT `album_picture_ibfk_1` FOREIGN KEY (`picture_id`) REFERENCES `picture` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `album_picture_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `album` (`id`) ON DELETE CASCADE; 

Mfg Splasch

[Forumsgast]

Was hindert Dich daran, im Subselect auf die Album-ID der Oberabfrage zu referenzieren? Und warum taucht Deine Album-Tabelle noch mal im Subselect auf?

Gruß Jens

[Forumsgast]

Ungetestet aus dem Bauch raus:

SELECT
    `tmpPicTable`.`thumb_filename`, `album`.`id`, `album`.`album`
    FROM `album` AS `a`
    LEFT JOIN `album_picture` AS `ap`
        ON `a`.`id` = `ap`.`album_id`
    LEFT JOIN (SELECT * FROM `picture` ORDER BY RAND()) AS `tmpPicTable`
        ON `ap`.`picture_id` = `tmpPicTable`.`id`
GROUP BY `ap`.`album_id`

[Forumsgast]

Danke G.Schuster werds gleich austesten.

Hab die Namen angepasst dir er nicht gefunden hat.

SELECT
 `pic`.`thumb_filename`,`album`.`id`,`album`.`album`
FROM
 `album`
LEFT JOIN `album_picture` ON `album`.`id` = `album_picture`.`album_id`
LEFT JOIN
  (SELECT * FROM `picture` ORDER BY RAND()) AS `pic`
   ON `album_picture`.`album_id` = `pic`.`id`
GROUP BY `album_picture`.`album_id` 

Bekomme jetzt nur immer die ersten 2 Alben gelistet und kein Zufalls Bild sondern immer das selbe Bild
Wenn ich das Group by ändere auf

GROUP BY `album`.`id` 

Bekomm ich wieder alle Alben aber auch immer das selbe Bild und Zwar immer das erste Bild des Album.
Es wird also kein Zufall mehr ausgewertet.

Hier also bekomm ich wieder alle Alben aber der Zufall bei Picture ist weg es wird immer von oben nach unten gelistet ohne zufall

SELECT
 `pic`.`thumb_filename`,`album`.`id`,`album`.`album`
FROM
 `album`
LEFT JOIN `album_picture` ON `album`.`id` = `album_picture`.`album_id`
LEFT JOIN
  (SELECT * FROM `picture` ORDER BY RAND()) AS `pic`
   ON `album_picture`.`picture_id` = `pic`.`id`
GROUP BY `album`.`id` 

Komischerweise wenn man auf den NULL verzichtet geht der Zufall wieder.
Aus irgend einen Grund mag er kein LEFT JOIN beim Zufallswert

SELECT
 `pic`.`thumb_filename`,`album`.`id`,`album`.`album`
FROM
 `album`
 JOIN `album_picture` ON `album`.`id` = `album_picture`.`album_id`
 JOIN
  (SELECT * FROM `picture` ORDER BY RAND()) AS `pic`
   ON `album_picture`.`picture_id` = `pic`.`id`
GROUP BY `album`.`id` 

Mfg Splasch

[Forumsgast]

So, umgebaut und getestet (3 Alben, 1. und 2. jeweils 3 Bilder, 3. mit 4 Bildern):

SELECT
 `pic`.`thumb_filename` AS `thumbFilename`,`album`.`id` AS `albumID`,`album`.`album` AS `albumTitle`
FROM
 `album`
LEFT JOIN
   (
      SELECT `album_picture`.`album_id` AS `albumID`, `picture`.`thumb_filename`
      FROM `album_picture`
      LEFT JOIN `picture`
      ON `album_picture`.`picture_id` = `picture`.`id`
      ORDER BY RAND()
   ) AS `pic`
   ON `album`.`id` = `pic`.`albumID`
GROUP BY `album`.`id`

+---------------+---------+------------+
| thumbFilename | albumID | albumTitle |
+---------------+---------+------------+
| 1             |       1 | a          |
| 6             |       2 | b          |
| 8             |       3 | c          |
+---------------+---------+------------+

+---------------+---------+------------+
| thumbFilename | albumID | albumTitle |
+---------------+---------+------------+
| 1             |       1 | a          |
| 5             |       2 | b          |
| 9             |       3 | c          |
+---------------+---------+------------+

+---------------+---------+------------+
| thumbFilename | albumID | albumTitle |
+---------------+---------+------------+
| 2             |       1 | a          |
| 5             |       2 | b          |
| 9             |       3 | c          |
+---------------+---------+------------+

[Forumsgast]

Danke Funktioniert.

Mfg Splasch