SQL Frage

[Bonsai]

3 Tabellen

Tabelle products
Felder : products_id, products_model, und weitere

Tabelle autors_to_products
Felder: products_id, autors_id

Tabelle autors
Felder: autors_id, autors_first_name, autors_last_name, und weitere

Ein product kann mehrere autors haben.

Wie bekommt man das in SQL hin, dass ich EINE Ergebniszeile für ein Produkt erhalte, die aber mehrere Autoren hat? Geht das überhaupt?

Linkback: https://www.modified-shop.org/forum/index.php?topic=34266.0

[webald]

http://beckdoor.de/mysql-mit-group_concat-zeilen-in-spalten-transponieren/04/11/2009/ so?

[Bonsai]

Das sieht sehr brauchbar aus!

[Bonsai]

Habe mal einen Test gefahren. Top!!!

Falls das jemand mal braucht ... genau so geht es:

Code: PHP  [Auswählen]

require_once('includes/application_top.php');

$sql = "SELECT p.products_model, pd.products_name, GROUP_CONCAT(a.autors_firstname, ' ', a.autors_lastname SEPARATOR '|' ) AS autors FROM products p LEFT JOIN products_description pd ON p.products_id = pd.products_id LEFT JOIN autors_to_products ap ON p.products_id=ap.products_id LEFT JOIN autors a ON ap.autors_id=a.autors_id WHERE p.products_id = 2373";

$result = xtc_db_query($sql);
$row = mysql_fetch_assoc($result);

echo '<pre>';
var_dump($row);
echo '</pre>';

 

liefert:

Code: PHP  [Auswählen]

array(3) {
  ["products_model"]=>
  string(8) "PD-06391"
  ["products_name"]=>
  string(21) "Eine Prise Gesundheit"
  ["autors"]=>
  string(65) "Sanja Lončar|Sabina Topolovec|Marija Kočevar Fetah|Nadja Baćac"
}

Oder wenn nur ein Autor:

Code: PHP  [Auswählen]

array(3) {
  ["products_model"]=>
  string(8) "PD-01756"
  ["products_name"]=>
  string(25) "Das Deutschland Protokoll"
  ["autors"]=>
  string(15) "Holger Fröhner"
}

Was ich noch rausfinden musste:
Innerhalb der Klammern von GROUP_CONCAT darf man beliebige Strings mit einbauen, den Seperator kann man selbst festlegen. Hinter der Klammer kann man noch mit "AS name" festlegen, wie der Schlüsselname assoziativen Array lautet.

Nochmals besten Dank!

[Bonsai]

Versteht jemand warum dieser Query

Code: SQL  [Auswählen]

SELECT p.products_fsk18, p.products_id, p.products_price, p.products_tax_class_id, p.products_image, p.products_quantity, p.products_vpe, p.pages, p.cover, p.products_subtitle, p.products_publishing_info, p.products_ebook_isbn, p.products_ebook_url, p.products_vpe_status, p.products_vpe_value, p.products_date_available, p.products_model, pd.products_name, pd.products_short_description, GROUP_CONCAT(a.autors_title, ' ', a.autors_firstname, ' ', a.autors_noble_title,' ', a.autors_lastname SEPARATOR '|' ) AS autor , xp.sort_order FROM products_xsell xp, products p, products_description pd LEFT JOIN autors_to_products ap ON xp.products_id=ap.products_id LEFT JOIN autors a ON ap.autors_id=a.autors_id WHERE xp.xsell_id = '2924' AND xp.products_id = p.products_id AND p.group_permission_1=1 AND p.products_id = pd.products_id AND pd.language_id = 2 AND TRIM(pd.products_name) != '' AND p.products_status = 1 ORDER BY xp.sort_order ASC

Den Fehler wirft:
1054 - Unknown column 'xp.products_id' in 'on clause'
 
p.products_id geht auch nicht ....

Warum kennt er die Spalte nicht, wenn sie im gleichen Query mehrfach referenziert ist?

[webald]

Auf Anhieb sehe ich das jetzt nicht.

Wie erstellst du denn die Abfragen? Ich hab mir für so was ein graphisches Tool geholt. Toad ist von Dell und für mysql freeware.

[Bonsai]

Das ist ein Query aus meiner angepassten Klasse Products.

Ich hab's, glaube ich. Mischen von unterschiedlichen JOIN typen mag er da wohl nicht. Hier ist so was beschrieben:
http://stackoverflow.com/questions/10483421/mysql-unknown-column-in-on-clause

Das liefert ein Ergebnis:

Code: SQL  [Auswählen]

 SELECT p.products_fsk18, p.products_id, p.products_price, p.products_tax_class_id, p.products_image, p.products_quantity, p.products_vpe, p.pages, p.cover, p.products_subtitle, p.products_publishing_info, p.products_ebook_isbn, p.products_ebook_url, p.products_vpe_status, p.products_vpe_value, p.products_date_available, p.products_model, pd.products_name, pd.products_short_description, GROUP_CONCAT( a.autors_title, ' ', a.autors_firstname, ' ', a.autors_noble_title, ' ', a.autors_lastname
SEPARATOR '|' ) AS autor, xp.sort_order
FROM (
products_xsell xp, products p, products_description pd
)
LEFT JOIN autors_to_products ap ON xp.products_id = ap.products_id
LEFT JOIN autors a ON ap.autors_id = a.autors_id
WHERE xp.xsell_id = '699'
AND xp.products_id = p.products_id
AND p.group_permission_1 =1
AND p.products_id = pd.products_id
AND pd.language_id =2
AND TRIM( pd.products_name ) != ''
AND p.products_status =1
ORDER BY xp.sort_order ASC
LIMIT 0 , 30

Die Klammern hinter dem FROM scheinen die Lösung zu sein. Ob das auch das richtige Ergebnis ist, sehe ich später, wenn mein Testshop komplett hochgeladen ist. Ich musste den mal plattmachen bevor ich weiter entwickele.

[Marcus Kreusch]

Hallo Bonsai,

das liegt daran, dass du an die Tabelle "products_description pd" joinst.
Das ON-Statement müsste dann also "pd.products_id = ap.products_id" lauten. Oder Du setzt den JOIN tatsächlich an die Tabelle "products_xsell xp" an.

Ich hoffe, das hilft

Viele Grüße
Marcus

[Bonsai]

Ja danke! Ich habe jetzt die Klasse products angepasst, und ich habe einen einzigen der Querys sauber zum laufen bekommen, den "Also purchased"

Bei den anderen kommen derzeit noch witzige Ergebnisse zustande. Mit JOIN stehe ich auf dem Kriegsfuß  

Das funktioniert:

Code: PHP  [Auswählen]

    $this->default_select ="p.products_fsk18,
                            p.products_id,
                            p.products_price,
                            p.products_tax_class_id,
                            p.products_image,
                            p.products_quantity,
                            p.products_vpe,
                                                        p.pages,
                                                        p.cover,
                                                        p.products_subtitle,
                                                        p.products_publishing_info,
                                                        p.products_ebook_isbn,
                                                        p.products_ebook_url,
                            p.products_vpe_status,
                            p.products_vpe_value,
                                                        p.products_date_available,
                            p.products_model,
                            pd.products_name,
                            pd.products_short_description,
                                                        GROUP_CONCAT(a.autors_title, ' ', a.autors_firstname, ' ', a.autors_noble_title,' ', a.autors_lastname, ' ' SEPARATOR '|' ) AS autor ";
 

....

Code: PHP  [Auswählen]

    $orders_query = "SELECT ".$this->default_select."
                       FROM ".TABLE_ORDERS_PRODUCTS." op1
                       JOIN ".TABLE_ORDERS_PRODUCTS." op2 on op2.orders_id = op1.orders_id
                       JOIN ".TABLE_ORDERS." o on o.orders_id = op2.orders_id
                       JOIN ".TABLE_PRODUCTS." p on p.products_id = op2.products_id
                       JOIN ".TABLE_PRODUCTS_DESCRIPTION." pd on pd.products_id = op2.products_id
                                           LEFT JOIN autors_to_products ap ON p.products_id=ap.products_id LEFT JOIN autors a ON ap.autors_id=a.autors_id
                      WHERE op1.products_id = ".$this->pID."
                        AND op2.products_id != ".$this->pID."
                        AND p.products_status = 1
                        AND trim(pd.products_name) != ''
                        AND pd.language_id = ".(int) $_SESSION['languages_id']
                            .$group_check
                            .$fsk_lock."
                   GROUP BY p.products_id
                   ORDER BY o.date_purchased desc
                      LIMIT ".MAX_DISPLAY_ALSO_PURCHASED;
 

Die anderen wie z.B.

Code: PHP  [Auswählen]

        $cross_query = "SELECT ".$this->default_select.",
                               xp.sort_order
                                                           
                          FROM (".TABLE_PRODUCTS_XSELL." xp,
                               ".TABLE_PRODUCTS." p,
                               ".TABLE_PRODUCTS_DESCRIPTION." pd)
                                                          LEFT JOIN autors_to_products ap ON p.products_id=ap.products_id LEFT JOIN autors a ON ap.autors_id=a.autors_id
                         WHERE xp.products_id = ".$this->pID."
                           AND xp.xsell_id = p.products_id "
                               .$fsk_lock
                               .$group_check."
                           AND p.products_id = pd.products_id
                           AND xp.products_xsell_grp_name_id='".$cross_sells['products_xsell_grp_name_id']."'
                           AND pd.language_id = ".(int)$_SESSION['languages_id']."
                           AND trim(pd.products_name) != ''
                           AND p.products_status = 1
                      ORDER BY xp.sort_order asc";
 

funktionieren nicht ....
[ Für Gäste sind keine Dateianhänge sichtbar ]

Der Standardquery für die Produktlisten liefert wenigstens die restlichen Daten auch wenn Autor komplett fehlt.

Ich denke ich muss die Komma Joins

Code: PHP  [Auswählen]

(".TABLE_PRODUCTS_XSELL." xp,
                               ".TABLE_PRODUCTS." p,
                               ".TABLE_PRODUCTS_DESCRIPTION." pd)

Umbauen zu so was:

Code: PHP  [Auswählen]

(".TABLE_PRODUCTS_XSELL." xp
                              JOIN ".TABLE_PRODUCTS." p ON ....
                              JOIN  ".TABLE_PRODUCTS_DESCRIPTION." pd ON ...

 

[Bonsai]

Jetzt habe ich Deine Aussage glaube ich richtig verstanden ....

Wenn ich 3 Tabellen habe, die über das gleiche Feld gejoint werden sollen, dann so:

Code: SQL  [Auswählen]

SELECT * FROM a JOIN b ON a.id=b.id JOIN c ON b.id=c.id

und nicht

Code: SQL  [Auswählen]

SELECT * FROM a JOIN b ON a.id=b.id JOIN c ON a.id=c.id

????

[Marcus Kreusch]

Hallo Bonsai,

zumindest wenn du an die normale Tabellenaufzählung joinst, muss sich der JOIN auf die letzte genannte Tabelle beziehen.

Also so:

Code: SQL  [Auswählen]

SELECT * FROM a, b JOIN c ON (c.id = b.id)

und nicht:

Code: SQL  [Auswählen]

SELECT * FROM a, b JOIN c ON (c.id = a.id)

[Bonsai]

Was ist wenn ich mehrere unterschiedliche JOINS habe?

Hier mal eine Kurzfassung von dem was ich versuche zu verstehen ...

Das wäre der original Query:

Code: SQL  [Auswählen]

SELECT p.products_manufacturers_model, p.products_id, p.products_ean, IF(ps.specials_new_products_price < p.products_price, ps.specials_new_products_price, p.products_price) AS smaller_price,
 pd.products_name
FROM products_description pd
JOIN products p
LEFT JOIN specials ps ON ps.products_id = p.products_id
JOIN products_to_categories p2c ON p2c.products_id = pd.products_id
WHERE p.products_status = '1'
AND p.products_id = pd.products_id
AND pd.language_id = '2'
AND p.group_permission_1=1
AND p2c.categories_id = '3'
ORDER BY p.products_sort ASC

Ich bräuchte jetzt eigentlich aus dem Ergebnis dieses Queries einen LEFT JOIN über 2 Tabellen:

Code: SQL  [Auswählen]

LEFT JOIN autors_to_products ap ON ap.products_id=pd.products_id
LEFT JOIN autors a ON a.autors_id=ap.autors_id

Wobei ich die Anzahl der Ergebniszeilen nicht verändern möchte. Also käme im SELECT ein

Code: SQL  [Auswählen]

GROUP_CONCAT( a.autors_title, ' ', a.autors_firstname)

zum Einsatz.

Was ich nicht verstehe .... egal wo ich meinen LEFT JOIN hinzufüge, reduziert sich die Anzahl der Treffer auf eine einzige Zeile, und die enthält dann alle Treffer der autors Tabelle von allen Zeilen des original Queries 

Edit:
Das hilft hier nicht:

Code: SQL  [Auswählen]

SELECT * FROM a, b JOIN c ON (c.id = b.id)

Weil ich ja bei autors oder specials so gut wie immer weniger Treffer als die Gesamtmenge habe. Da kann ich mich dann doch nicht auf die Tabelle davor beziehen?

[Marcus Kreusch]

Hallo Bonsai,

hier mein Vorschlag:

Code: SQL  [Auswählen]

SELECT p.products_manufacturers_model, p.products_id, p.products_ean, IF(ps.specials_new_products_price < p.products_price, ps.specials_new_products_price, p.products_price) AS smaller_price,
 pd.products_name,
GROUP_CONCAT( a.autors_title, ' ', a.autors_firstname) AS authors
FROM products_description pd
JOIN products p
LEFT JOIN autors_to_products ap ON ap.products_id=p.products_id
LEFT JOIN autors a ON a.autors_id=ap.autors_id
LEFT JOIN specials ps ON ps.products_id = p.products_id
JOIN products_to_categories p2c ON p2c.products_id = pd.products_id
WHERE p.products_status = '1'
AND p.products_id = pd.products_id
AND pd.language_id = '2'
AND p.group_permission_1=1
AND p2c.categories_id = '3'
GROUP BY p.products_id
ORDER BY p.products_sort ASC
 

Zur Erklärung: Um nicht nur eine Zeile zu erhalten, musst du explizit angeben, wonach Gruppiert werden soll - in deinem Fall nach Produkten (GROUP BY p.products_id). Sonst zieht er alles zu einem Datensatz zusammen.

Wegen der JOINs: Es ist so, dass die "virtuelle Tabelle" durch den JOIN vergrößert wird. Du kannst dich also bei beliebig vielen JOINs auf alles beziehen, was innerhalb der gleichen JOIN-Gruppe vorhanden ist, aber nicht auf etwas, was aus anderen Bereichen der durch Kommas getrennten FROM-Gruppe kommt.
Verstehst du, was ich meine?

Beste Grüße
Marcus

[Bonsai]

Das war ja einfach. Ich hatte an der völlig falschen Stelle nach dem Problem gesucht. 

Jetzt kapiere ich auch warum einer der Queries auf Anhieb funktioniert hat. Da war schon ein GROUP BY p.products_id enthalten.

Vielen Dank, jetzt habe ich es glaube ich verstanden. Ich geh gleich mal die Cross selling Queries anpassen.

[Marcus Kreusch]

Gerne, Bonsai!
Dann mal gutes Gelingen!