Sql Zufall Bild von Album

[splasch]

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


[/php]

Durch den Zufall setzt er es nicht in verbindung mit der Zuweisung des Alben Fotos.

Die 3 Tabellen sehen so aus.

[php]

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

[streffin]

Ich hab jetzt keine MySql DB griffbereit (schreib das, das nächste mal bitte dazu), aber das hier sollte normal tun:

SELECT a.album, sq.*

FROM album a 

LEFT JOIN (

      SELECT 

      FROM album_picture ap INNER JOIN picture p on ap.picture_id = p.id

      WHERE ap.ablum_id = a.id

      ORDER BY RAND() LIMIT 1 

)sq ON a sq.album_id = a.id

MySql ist nicht ganz mein Steckenpferd, daher keine garantie für die Syntax

Hier zum Nachlesen des Prinzips dahinter :

MySQL subquery to refer to field in parent query - Stack Overflow

Gruß

Sven

[splasch]

Danke und werd ich das nächste mal machen.

Mfg Splasch