Re: Subquery mit zufälligem Wert
am 07.05.2006 14:59:22 von Dominik Echterbruch
Nikolai Onken wrote:
> Hallo,
>
> ich bastel an einer query die z.B. alle Events aus einer Stadt lädt.
> Jetzt hat jede Stadt z.B. Clubs, die wiederum Bilder haben.
> Wenn ich die Stad ansehe möchte ich ein zufälliges Bild aus einem der
> Clubs in ser Stadt anzeigen. Das funktioniert leider nicht.. Hier meine
> jetzige Version:
[längliche Abfrage]
Ich habe nicht ganz verstanden, was diese Abfrage bewirken soll (was
wohl ursächlich daran liegt, daß ich sie mir gar nicht erst genau
angeschaut habe), deshalb mal ein stark vereinfachtes Schema:
CREATE TABLE `stadt` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) default NULL,
PRIMARY KEY (`id`)
);
INSERT INTO stadt (name) values ('Frankfurt'), ('Hanau'), ('Wiesbaden');
SELECT * FROM stadt;
+----+-----------+
| id | name |
+----+-----------+
| 1 | Frankfurt |
| 2 | Hanau |
| 3 | Wiesbaden |
+----+-----------+
CREATE TABLE `club` (
`id` int(10) unsigned NOT NULL auto_increment,
`stadtid` int(10) unsigned NOT NULL default '0',
`name` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `stadtid` (`stadtid`)
);
INSERT INTO club (stadtid, name) values (1, "Jazil"), (1, "Goldfish
Club"), (1, "Cocoon Club"), (2, "Cafe Pirot"), (2, "Black Inn"), (3,
"Euro Palace"), (3, "X-tra");
SELECT * FROM club;
+----+---------+---------------+
| id | stadtid | name |
+----+---------+---------------+
| 1 | 1 | Jazil |
| 2 | 1 | Goldfish Club |
| 3 | 1 | Cocoon Club |
| 4 | 2 | Cafe Pirot |
| 5 | 2 | Black Inn |
| 6 | 3 | Euro Palace |
| 7 | 3 | X-tra |
+----+---------+---------------+
CREATE TABLE `bild` (
`id` int(10) unsigned NOT NULL auto_increment,
`clubid` int(10) unsigned NOT NULL default '0',
`pfad` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `clubid` (`clubid`)
);
INSERT INTO bild (clubid, pfad) values (1, "ffm_jazil_1.jpg"), (1,
"ffm_jazil_2.jpg"), (2, "ffm_goldfish_1.jpg"), (2,
"ffm_goldfish_2.jpg"), (3, "ffm_cocoon_1.jpg"), (3, "ffm_cocoon_2.jpg"),
(3, "ffm_cocoon_3.jpg"), (4, "hu_pirot_1.jpg"), (4, "hu_pirot_2.jpg"),
(5, "hu_black_1.jpg"), (5, "hu_black_.jpg"), (6, "wi_euro_1.jpg"), (6,
"wi_euro_2.jpg"), (6, "wi_euro_3.jpg"), (7, "wi_xtra_1.jpg"), (7,
"wi_xtra_2.jpg");
SELECT * FROM bild;
+----+--------+--------------------+
| id | clubid | pfad |
+----+--------+--------------------+
| 1 | 1 | ffm_jazil_1.jpg |
| 2 | 1 | ffm_jazil_2.jpg |
| 3 | 2 | ffm_goldfish_1.jpg |
| 4 | 2 | ffm_goldfish_2.jpg |
| 5 | 3 | ffm_cocoon_1.jpg |
| 6 | 3 | ffm_cocoon_2.jpg |
| 7 | 3 | ffm_cocoon_3.jpg |
| 8 | 4 | hu_pirot_1.jpg |
| 9 | 4 | hu_pirot_2.jpg |
| 10 | 5 | hu_black_1.jpg |
| 11 | 5 | hu_black_.jpg |
| 12 | 6 | wi_euro_1.jpg |
| 13 | 6 | wi_euro_2.jpg |
| 14 | 6 | wi_euro_3.jpg |
| 15 | 7 | wi_xtra_1.jpg |
| 16 | 7 | wi_xtra_2.jpg |
+----+--------+--------------------+
Was du oben beschrieben hast (zufälliges Bild irgendeines Clubs in der
angegebenen Stadt), würdest du dann sehr schnell mit folgender Abfrage
erreichen:
SELECT bild.pfad
FROM stadt
INNER JOIN club ON club.stadtid = stadt.id
INNER JOIN bild ON bild.clubid = club.id
WHERE stadt.id =
ORDER BY RAND()
LIMIT 1;
Voraussetzung: es gibt mindestens ein Bild und einen Club pro Stadt.
Wenn also keine Ergebniszeile zurück kommt, mußt du das abfangen und
z.B. ein Standardbild einfügen.
Wenn du das jetzt in einer Übersicht aller Städte machen möchtest,
kannst du es ja durch eine kleine Änderung einfach dazu basteln:
SELECT stadt.name, (
SELECT bild.pfad
FROM stadt AS st
-- ^^^^^ die kleine Änderung
INNER JOIN club ON club.stadtid = st.id
-- ^^ und hier
INNER JOIN bild ON bild.clubid = club.id
WHERE st.id = stadt.id
--^^^^^^^^^^^^^^^^^^^^^^ und dieses
ORDER BY RAND()
LIMIT 1
) AS pfad
FROM stadt;
+-----------+------------------+
| name | pfad |
+-----------+------------------+
| Frankfurt | ffm_cocoon_3.jpg |
| Hanau | hu_black_1.jpg |
| Wiesbaden | wi_euro_2.jpg |
+-----------+------------------+
Hier kommt dann zwar auf jeden Fall eine Zeile zurück, aber der Wert für
pfad kann NULL werden, wenn es in einer Stadt keine Clubs gibt und/oder
keine Bilder:
DELETE FROM club WHERE stadtid = 2;
DELETE FROM bild WHERE clubid >= 4;
SELECT * FROM club;
+----+---------+---------------+
| id | stadtid | name |
+----+---------+---------------+
| 1 | 1 | Jazil |
| 2 | 1 | Goldfish Club |
| 3 | 1 | Cocoon Club |
| 6 | 3 | Euro Palace |
| 7 | 3 | X-tra |
+----+---------+---------------+
SELECT * FROM bild;
+----+--------+--------------------+
| id | clubid | pfad |
+----+--------+--------------------+
| 1 | 1 | ffm_jazil_1.jpg |
| 2 | 1 | ffm_jazil_2.jpg |
| 3 | 2 | ffm_goldfish_1.jpg |
| 4 | 2 | ffm_goldfish_2.jpg |
| 5 | 3 | ffm_cocoon_1.jpg |
| 6 | 3 | ffm_cocoon_2.jpg |
| 7 | 3 | ffm_cocoon_3.jpg |
+----+--------+--------------------+
SELECT stadt.name, (
SELECT bild.pfad
FROM stadt AS st
INNER JOIN club ON club.stadtid = st.id
INNER JOIN bild ON bild.clubid = club.id
WHERE st.id = stadt.id
ORDER BY RAND()
LIMIT 1
) AS pfad
FROM stadt;
+-----------+------------------+
| name | pfad |
+-----------+------------------+
| Frankfurt | ffm_cocoon_3.jpg |
| Hanau | NULL |
| Wiesbaden | NULL |
+-----------+------------------+
Grüße,
Dominik
--
Norbert Melzer in d.c.d.mysql:
F: Wie verstehe ich diese FAQ am besten?
A: Studieren Sie Datanbank-Design und lesen Sie anschliessend alles nochmal