Ankündigung

Einklappen
Keine Ankündigung bisher.

inner/outer join - sql-Abfrage

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • inner/outer join - sql-Abfrage

    Hallo zusammen,

    ich sitze vor einer sql-Abfrage, die ich zugegebenermaßen nicht verstehe.
    Die Erklärung hilft mir nicht weiter, auch wenn dort alles steht, was nötig ist.

    Hier der Text:

    $filterlist_sql = "select distinct c.categories_id as id, coalesce(concat(pcd.categories_name, ' :: ', cd.categories_name), cd.categories_name) as name from " . TABLE_PRODUCTS . " p inner join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on (p.products_id = p2c.products_id) inner join " . TABLE_CATEGORIES . " c on (p2c.categories_id = c.categories_id) inner join " . TABLE_CATEGORIES_DESCRIPTION . " cd on (p2c.categories_id = cd.categories_id) left outer join " . TABLE_CATEGORIES_DESCRIPTION . " pcd on (c.parent_id = pcd.categories_id and pcd.language_id = '" . (int)$languages_id . "') where p.products_status = '1' and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' order by name";

    ... macht genau das, was ich möchte, aber...

    How it works:

    The original version gets the category names and id numbers for the dropdown menu by a series of joins. This is needed because the category names are in the categories_description table and we have to get there from the products table. So, they join as follows: products to products_to_categories on the products_id field, products_to_categories to categories on the categories_id field, and prodcuts_to_categories to categories_description on the categories_id field.

    What I did is add another join to the categories_description table, but on the parent_id field from the categories table instead. This second join to the categories_description table gets us the name of the parent category which is then tacked on to the category name with a concat function.

    However, the second join has to be an outer join, otherwise we won't get back any categories where there is no parent. Specifing one join as outer means you have to change the join syntax and explicitly state the others as inner. The coalesce function takes care of returning the category name when the parent is null.

    You can set the seperator character in the concat function. I used ': ' so it looks like: parent: category, but you can use whatever you like: ' :: ', ' | ', ' -> ', etc.

    Just make sure you use single quotes around the seperator character since a double quote will terminate the sql string and give you a parse error. Also, if you have categories nested two or more levels deep, this will only grab one category up from the one with the products. In other words, if you have:

    Category : Subcategory1 : Subcategory2 : Products

    It will show up as:

    Subcategory1 : Subcategory2

    -------------------------------------------------------------------------


    Was muss ich in der $filterlist_sql ändern, um eine dritte Unterkategorie abzufragen und darzustellen?


    Ich hoffe, ich strapaziere damit nicht Eure Geduld
    und vielen Dank Euch

    Oliver


  • #2
    Was hast du denn schon probiert? Rück doch mal das SQL vernünftig ein.
    You know, my wife sometimes looks at me strangely. „Duncan“, she says, „there's more to life than Solaris“. Frankly, it's like she speaks another language. I mean, the words make sense individually, but put them together and it's complete nonsense.

    Kommentar


    • #3
      Zitat von chorn Beitrag anzeigen
      Was hast du denn schon probiert? Rück doch mal das SQL vernünftig ein.
      Danke für Deine Antwort.

      Ich bin mir nicht sicher, ob ich das so richtig mache:


      $filterlist_sql = "select distinct c.categories_id as id, -> keine Dubletten

      coalesce(concat(pcd.categories_name, ' :: ', cd.categories_name), cd.categories_name) as name from " . TABLE_PRODUCTS .
      -> nur Werte ungleich Null (verknüpfe die Strings()


      " p inner join " . TABLE_PRODUCTS_TO_CATEGORIES .

      " p2c on (p.products_id = p2c.products_id) inner join " . TABLE_CATEGORIES .

      " c on (p2c.categories_id = c.categories_id) inner join " . TABLE_CATEGORIES_DESCRIPTION .

      " cd on (p2c.categories_id = cd.categories_id) left outer join " . TABLE_CATEGORIES_DESCRIPTION .

      " pcd on (c.parent_id = pcd.categories_id and pcd.language_id = '" . (int)$languages_id . "')

      where p.products_status = '1' and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] .
      "' order by name";


      Wenn ich das richtig(?) verstehe und in Klammern darstelle, passiert folgendes --> X=kartesisches Produkt

      (((TABLE_PRODUCTS X TABLE_PRODUCTS_TO_CATEGRORIES) X TABLE_CATEGORIES) X TABLE_CATEGORIES_DESCRIPTIONS) X TABLE_CATEGORIES_DESCRIPTIONS


      Mein Versuch sieht so aus:

      $filterlist_sql = "select distinct c.categories_id as id,

      coalesce(concat(p2cd.categories_name, ' :: ', pcd.categories_name, ' :: ', cd.categories_name), cd.categories_name) as name from " . TABLE_PRODUCTS .

      " p inner join " . TABLE_PRODUCTS_TO_CATEGORIES .

      " p2c on (p.products_id = p2c.products_id) inner join " . TABLE_CATEGORIES .

      " c on (p2c.categories_id = c.categories_id) inner join " . TABLE_CATEGORIES_DESCRIPTION .

      " cd on (p2c.categories_id = cd.categories_id) left outer join " . TABLE_CATEGORIES_DESCRIPTION .

      " pcd on (c.parent_id = pcd.categories_id and pcd.language_id = '" . (int)$languages_id . "').

      " p2cd on (c.parent_id = p2cd.categories_id and p2cd.language_id = '" . (int)$languages_id . "')

      where p.products_status = '1' and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] .
      "' order by name";


      Vielen Dank für Deine Hilfe
      Oliver

      Nachtrag:
      Diese Abfrage erzeugt einen SQL-Syntax-Error

      Kommentar


      • #4
        Schön, dass du gewisse Teile farblich hervorhebst, aber wir haben hier auch BB-Codes für PHP- und Allgemeinen Code.
        Zitat von nikosch
        Macht doch alle was Ihr wollt mit Eurem Billigscheiß. Von mir aus sollen alle Eure Server abrauchen.

        Kommentar


        • #5
          OK - das mache ich das nächste Mal besser!
          Danke

          Kommentar


          • #6
            Den Syntax-Error habe ich jetzt herausbekommen:

            Code:
            $filterlist_sql = "select distinct c.categories_id as id,
             coalesce(concat(p2cd.categories_name, ' -> ', pcd.categories_name, ' -> ', cd.categories_name), cd.categories_name) as name from " . TABLE_PRODUCTS . 
            
            " p inner join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on (p.products_id = p2c.products_id)
            
             inner join " . TABLE_CATEGORIES . " c on (p2c.categories_id = c.categories_id)
            
             inner join " . TABLE_CATEGORIES_DESCRIPTION . " cd on (p2c.categories_id = cd.categories_id) 
            
            left outer join " . TABLE_CATEGORIES_DESCRIPTION . " pcd on (c.parent_id = pcd.categories_id and pcd.language_id = '" . (int)$languages_id . "')" . " 
            
            left outer join " . TABLE_CATEGORIES_DESCRIPTION . " p2cd on (c.parent_id = p2cd.categories_id and p2cd.language_id = '" . (int)$languages_id . "')
            
             where p.products_status = '1' and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . 
            "' order by name";
            Es wird mir
            SubKategorie 1 -> Subkategorie 1 -> Subkategorie 2 angezeigt.
            Ich benötige aber Subkategorie 1 - Subkategorie 2 - Subkategorie 3

            order by limit 0,1 funktioniert nicht - left join bei pcd / p2cd ändert nichts.

            Wie kann ich p2cd dazu bringen, den Wert vor pcd anzunehmen?

            Danke
            Oliver

            Kommentar

            Lädt...
            X