#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    Germany
    Posts
    233
    Rep Power
    16

    Iterator for double entries


    Hi all,

    I've a tricky question. I am selecting entries from a variety of tables. In this case shop articles.
    There is always a base article which I get from the article table. There may be further variants of the article, which are located in variant table and all refer the main article.

    In the end I want to get all articles and variants as one result line. Which works fine.

    e.g.

    article_table
    arId, arName, ...
    VA17, Test article

    article_variant_table
    avaId,avaRefArId,avaPrice
    123,VA17,100
    234,VA17,200
    345,VA17,300

    Which results in three result lines:
    VA17,Test article,100
    VA17,Test article,200
    VA17,Test article,300

    So far so good. What I want to achieve is that these double entries gain an iterator which starts at the 1st double entry. The result should look like this:

    VA17,Test article,100
    VA17.1,Test article,200
    VA17.2,Test article,300

    Important to note: The iterator should reset for each double entry:

    VA17,Test article,100
    VA17.1,Test article,200
    VA17.2,Test article,300
    VA18,Test article 2,50
    VA18.1,Test article 2,70

    This is what my query looks like at the moment:
    Code:
    SELECT concat('VA',arId) AS ordernumber, concat('VA',arId) AS mainnumber, titeltext.spText AS name, untertiteltext.spText AS additionalText, "Vahle Trauerwaren" AS supplier, "19.00" AS tax, Artikelvarianten.avaPreis AS price_EK, "" AS pseudoprice_EK, "" AS baseprice_EK, "1" AS from_EK, "beliebig" AS to_EK, "" AS price_H, "" AS pseudoprice_H, "" AS baseprice_H, "" AS from_H, "" AS to_H, 
    CASE 
    	WHEN `arAktiv` = 'normal' THEN '1' 
        ELSE '' 
    END AS 'active', '0' AS instock, '0' AS stockmin, '' AS description, descriptiontext.spText AS description_long, '' AS shippingtime, '' AS added, '' AS changed, '' AS releasedate, '0' AS shippingfree, 
    '0' AS topseller, '' AS keywords, '1' AS minpurchase, '' AS purchasesteps, '' AS maxpurchase, '' AS purchaseunit, '' AS referenceunit, '' AS packunit, '' AS unitID, '' AS pricegroupID, '0' AS pricegroupActive, 
    '0' AS laststock, '' AS suppliernumber, '0' AS weight, '' AS width, '' AS height, '' AS length, '' AS ean, '' AS similar, 
    arId AS configuratorsetID, '' AS configuratortype, concat('Option: ',variantentext.spText) AS configuratorOptions, 
    '' AS categories, '' AS propertyGroupName, '' AS propertyValueName, '' AS accessory, '' AS imageUrl, '' AS main, '' AS attr1, '' AS attr2, '' AS attr3, '' AS purchasePrice, '' AS metatitle 
    FROM `Artikel` 
    LEFT JOIN Sprache AS titeltext ON titeltext.spId = Artikel.arName 
    LEFT JOIN Sprache AS untertiteltext ON untertiteltext.spId = Artikel.arUntertitel 
    LEFT JOIN Sprache AS descriptiontext ON descriptiontext.spId = Artikel.arDescription 
    LEFT JOIN Artikelvarianten ON Artikelvarianten.avaRefArId = Artikel.arId 
    LEFT JOIN ArtikelAttreg2Varianten ON ArtikelAttreg2Varianten.refAvaId = Artikelvarianten.avaId 
    LEFT JOIN Sprache AS variantentext ON variantentext.spPrimaryId = ArtikelAttreg2Varianten.refAegId AND variantentext.spTable = 'ArtikelAttreg' 
    WHERE arAktiv = 'normal' 
    ORDER BY Artikel.arID ASC
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,757
    Rep Power
    4288
    In the end I want to get all articles and variants as one result line.
    Code:
    SELECT article_table.arId
         , article_table.arName  
         , GROUP_CONCAT(article_variant_table.avaPrice) AS prices
      FROM article_table
    INNER  
      JOIN article_variant_table
        ON article_variant_table.avaRefArId = article_table.arId
    GROUP
        BY article_table.arId
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    Germany
    Posts
    233
    Rep Power
    16
    Sorry, English is not my native language. I meant that each variant must have its' own result line.
    I have to use the result to import it into a shop system.

    So the result for one product with two variants (in sum 3 individual articles) still has to look like this:

    VA17,Test article,100
    VA17.1,Test article,200
    VA17.2,Test article,300

    but that this part ".1" and ".2" is added to every entry but the first one.

    Sorry for my poor description. :/
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,757
    Rep Power
    4288
    Originally Posted by winddancer
    Sorry for my poor description. :/
    entschuldigung war ja nicht nötig, deine beispiele waren klar, ich wollte nur GROUP_CONCAT wieder vorstellen
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    Germany
    Posts
    233
    Rep Power
    16
    Originally Posted by r937
    entschuldigung war ja nicht nötig, deine beispiele waren klar, ich wollte nur GROUP_CONCAT wieder vorstellen
    Danke für den Hinweis, aber inwieweit hilft mir das weiter? Ich will ja nicht die Preise zusammenführen, ich muss die ID ab der ersten Wiederholung um einen Iterator ergänzen.

    Sprich aus VA17, VA17, VA17 muss VA17, VA17.1, VA17.2 usw. werden.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,757
    Rep Power
    4288
    Originally Posted by winddancer
    ... muss VA17, VA17.1, VA17.2 usw. werden.
    leider kann ich nicht damit helfen...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo