Zwei Tabellen über JOIN / Produkt und Bild, wie das aktuellste Bild holen?

[Boris]

Moin,

mit Hilfe dieser Query hole ich mir zu einem Produkt aus einer zweiten Tabellen noch ein Produktbild:

        SELECT
            t1.id,
            t1.order_nr,
            t1.name,
            t1.createdate,
            t1.status,
            t2.filename,
            t2.title
        FROM
            cms_products_desc t1
        LEFT JOIN
            cms_products_pics t2
        ON
            t1.id = t2.product_id
        WHERE
            subcat_id=".quote_smart($_POST['subcat_id'])."
        GROUP BY
            t1.id
        ORDER BY
            t1.name ASC 

Soweit, so gut - so erhalte ich halt ein Bild des Produktes. Nur kann es ja auch mehrere Produktbilder haben und ich hätte gern, dass nicht irgendein Bild angezeigt wird, sondern z.B. das aktuellste von allen.

Hat jemand eine Idee?

[Peter]

Wenn die BIldertabelle eine auto_incr ID hat, würde immer das BIld mit der höchsten ID das neueste sein:
ORDER BY t1.name ASC, t2.ID DESC

[Boris]

Das war ja auch mein erster Versuch, aber das ändert überhaupt nichts.

Die Tabelle mit den Bildern hätte auch eine Spalte priority (damit kann man selbst eine Reihenfolge wählen) und createdate (DATETIME) ... aber nichts davon greift über ORDER BY.

[Peter]

hm, das liegt ja an dem group by weil der immer das erste element nimmt.
WIe man es löst, weiß ich jetzt auch nicht.
Eine Spalte "aktiv" (bei der du das letzte Bild aktiv setzt), könnte eine Umgehungsmöglichkeit sein.

Aber vielleicht kommt ja noch ein SQL-Guru vorbei.

[Peter]

Oder schau mal hier vorbei:
http://www.issociate.de/board/post/2...Y_Problem.html

Das ist doch genau dein Problem.

[CharlyB]

Ist die t1.id ein Identifyer für einen Eintrag einer Produktbeschreibung oder ein Produkt? Denn ist die ID eindeutig für die Tabelle cms_products_desc leuchtet mir die GROUP BY Klausel nicht so recht ein.

[Boris]

Ehrlich gesagt hab ich halt irgendwas bei GROUP BY genommen ... ich wusste nicht, was ich da angeben sollte.

[CharlyB]

GROUP BY kannst du getrost weglassen, da in der Abfrage keinerlei Aggregatfunktionen benutzt werden und diese somit unnütz ist. Aber nochmal die Frage: Steht in der Tabelle cms_products_desc für jedes Produkt 0+ Beschreibungen, oder ist das gleichzeitig deine Produkttabelle?

[Boris]

In cms_products_desc steht alles generelle zum Produkte - Kategorie, Titel, Beschreibung und Status.

Der Rest (Farben, Preise und halt Produktbilder) sind in jeweils anderen Tabellen, die ich je nach Bedarf halt JOINe

Wenn ich das GROUP BY t1.id weg nehme, wird mir allerdings 4x das gleiche Produkt angezeigt - mit jeweils von einem der 4 dazugehörigen Produktbilder.

[CharlyB]

Ha.. ich glaube ich verstehe. Vom DB-Design ist es zwar nicht das beste sämtliche Produktinformationen immer wieder (redundant) zu speichern, nur weil sich die Beschreibung bzw. das Produktbild ändert, aber das ist ja nicht das Thema.

Nehm' also wieder das GROUP BY mit rein und ändere im SELECT das createdate in MAX(createdate):

SELECT
            t1.id,
            t1.order_nr,
            t1.name,
            max(t1.createdate) as createdate,
            t1.status,
            t2.filename,
            t2.title
        FROM
            cms_products_desc t1
        LEFT JOIN
            cms_products_pics t2
        ON
            t1.id = t2.product_id
        WHERE
            subcat_id=".quote_smart($_POST['subcat_id'])."
        GROUP BY
            t1.id
        ORDER BY
            t1.name ASC

Sollte das aus mir z.Z. unerklärlichen Gründen (Feiertag und gerade mal 7:30 ) nicht funktionieren, dürfte eine geschachtelte Abfrage weiterhelfen. Diese setzt allerdings mind. die Version 4.1 von MySQL voraus.

SELECT *, max(createdate) FROM
(            
SELECT
            t1.id,
            t1.order_nr,
            t1.name,
            t1.createdate,
            t1.status,
            t2.filename,
            t2.title
        FROM
            cms_products_desc t1
        LEFT JOIN
            cms_products_pics t2
        ON
            t1.id = t2.product_id
        WHERE
            subcat_id=".quote_smart($_POST['subcat_id'])."
) a
GROUP BY a.id
ORDER BY a.name ASC

Gruß,
Jan

[Boris]

Also wahrscheinlich war es zu früh für dich - was soll ich denn mit max(t1.createdate) as createdate? Es gibt doch nur ein einziges Produkt in er Tabelle t1, also auch nur ein Datum davon.

Logisch wäre es, das neuste Datum aus der Tabelle mit den (mehreren) Bildern zu selektiere, sprich max(t2.createdate) as createdate ... was mir halt das aktuellste Datum eines Bildes gibt. Und was mach ich damit, wenn ich das aktuellste Bild haben will?

Vom DB-Design ist es zwar nicht das beste sämtliche Produktinformationen immer wieder (redundant) zu speichern

Tu ich doch garnicht? Pro Produkt und dessen Hauptinformationen existiert nur ein Eintrag. Der Rest, der variabel ist wie Bilder, verschiedene Preise, verschiedene Rabatte etc. sind halt in andere Tabellen verteilt. Wie soll das denn "besser" gehen?

Hier mal zwei Tabellen + Inhalt:

Produkttabelle:

CREATE TABLE `cms_products_desc` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `order_nr` varchar(100) NOT NULL,
  `subcat_id` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `createdate` datetime NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Produkttabelle Beschreibung' AUTO_INCREMENT=11 ;

-- 
-- Dumping data for table `cms_products_desc`
-- 

INSERT INTO `cms_products_desc` VALUES (1, '1234#232-34', 1, 'Mega Edelstahl Deluxe 2', '<p>Das ist mal eine echt tolle Uhr</p> <ul> <li>Toll</li> <li>Krass</li> <li>Stark</li> </ul> <p>Naja, und so ... oder gehts weiter?Wie geil ...</p>', '2007-03-30 17:35:50', 2); 

Und Bildertabelle:

CREATE TABLE `cms_products_pics` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `product_id` int(10) unsigned NOT NULL COMMENT 'ID des Produktes',
  `title` varchar(255) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `priority` mediumint(9) NOT NULL COMMENT 'Anzeigenreihenfolge',
  `createdate` datetime NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Produkttabelle Bilder' AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `cms_products_pics`
-- 

INSERT INTO `cms_products_pics` VALUES (5, 1, 'Pullunder Schwarz', 'pullunder_d57f6d8cb6.jpg', 25, '2007-04-04 11:56:14', 1);
INSERT INTO `cms_products_pics` VALUES (6, 1, 'Neues Bild', 'duo_23f713a820.jpg', 200, '2007-04-04 12:05:46', 1); 

[alexf812]

du brauchst doch ein merkmal, an dem du festmachst das dies das aktuelle bild ist....wie schon oben gesagt wäre das ja die id oder create_date... ich würde das group by rausnehmen und dann kannst du mit order by id und limit 1 ja das aktuellste bild bekommen

SELECT t1.id, t1.order_nr, t1.name, t1.createdate, t1.status, t2.filename, t2.title
FROM cms_products_desc t1
INNER JOIN cms_products_pics t2 ON t1.id = t2.product_id
ORDER BY t2.id DESC
LIMIT 0 , 1

[CharlyB]

Hmmm... stimmt, dass ist dann doch nicht ganz so trivial.
Stimmt auch: mit max(createtime) bekommt man auch nur das max. Datum, die anderen selektierten Werte stehen damit nicht im Zusammenhang, auch wenn es so erscheint. - Häufiger Fehler, glatt vergessen.

Die LIMIT Variante funktioniert, aber nur für je ein Produkt und nicht eine ganze Produktgruppe.

Der Ehrgeiz hat mich jetzt gepackt.... werde mal versuchen eine Lösung zu basteln. Wenn ich erfolgreich bin, werde ich es hier posten. - Allerdings ist das Fernsehprogramm einfach zu gut heute... kann also etwas dauern

Und ja, war wohl doch ein wenig früh...

Jan

[CharlyB]

Ok, ein Ansatz. Ob es einfacher geht, kann ich momentan nicht sagen. Aber diese Abfrage scheint zu funktionieren. (MySQL 4.1 ist mind. Voraussetzung)

SELECT `cms_products_desc`.`id`,
       `cms_products_desc`.`name`,
       main.*
       
FROM `cms_products_desc` LEFT JOIN (
    SELECT `cms_products_pics`.id, `cms_products_pics`.`filename`, sub.*
    FROM
    `cms_products_pics` JOIN
                    (
                    SELECT product_id, max(createdate) as createdate
                    FROM `cms_products_pics`
                    GROUP BY product_id
                    ) sub
                    ON (`cms_products_pics`.`product_id` = sub.product_id AND `cms_products_pics`.`createdate` = sub.createdate)
    )main
    ON `cms_products_desc`.`id` = main.product_id

[Stuck Mojo]

Erwartest du einen einzigen Eintrag von deinem Query oder soll es eine Liste sein? Existiert zu jedem Produkt ein Bild?

Gruß
Jan