SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 28 of 28

Thread: SQL syntax

  1. #26
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    313
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is my current query:

    PHP Code:
        $select '    SELECT 
                    items.itemID, 
                    itemTitle, 
                    itemSKULadies, 
                    itemSKUMen, 
                    itemDescLadies, 
                    itemDescMen,  
                    itemPrice, 
                    itemColours, 
                    categories.category, 
                    suppliers.supplier, 
                    itemTypes.itemType, 
                    sizesMen.size AS Msize, 
                    sizesLadies.size AS Lsize, 
                    sc.subcategories,
                    ss.siteSections'
    ;
        
    $from   '    FROM items 
                    LEFT JOIN categories ON categories.catID=items.catID 
                    LEFT JOIN suppliers ON suppliers.supplierID=items.supplierID 
                    LEFT JOIN itemTypes ON itemTypes.itemTypeID=items.itemTypeID 
                    LEFT JOIN sizesMen ON sizesMen.sizeMenID=items.sizeMenID 
                    LEFT JOIN sizesLadies ON sizesLadies.sizeLadiesID=items.sizeLadiesID 
                    LEFT JOIN
                    (SELECT item_to_subcat.itemID,
                    GROUP_CONCAT(subcategories.subcategory SEPARATOR ", ") AS subcategories
                    FROM item_to_subcat
                    INNER JOIN subcategories ON subcategories.subcatID = item_to_subcat.subcatID
                     GROUP BY item_to_subcat.itemID ) AS sc ON sc.itemID = items.itemID
                    LEFT JOIN
                    (SELECT item_to_siteSection.itemID, GROUP_CONCAT(siteSections.siteSection SEPARATOR ", ") AS siteSections
                     FROM item_to_siteSection
                     INNER JOIN siteSections ON siteSections.siteSectionID = item_to_siteSection.siteSectionID
                     GROUP BY item_to_subcat.itemID ) AS ss ON ss.itemID = items.itemID'

    I can't see where I copied it incorrectly?

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    my apologies, i had a typo

    change last line
    from GROUP BY item_to_subcat.itemID ) AS ss
    to GROUP BY item_to_siteSection.itemID ) AS ss
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    313
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again - that's all working really well now. I really need to study that nested SQL so I can use it for other things.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •