SQL syntax

Databases
21

Yeah it’s possible.

Why would you say it would return 6 rows? If there are 2 site sections and 3 subcategories and it was returning duplicates, wouldn’t it be returning 10 rows?

So how do you prevent the GROUP_CONCAT from containing duplicates?

22

no, 2 times 3 equals 6, not 10 :slight_smile:

you could push the grouping into subqueries

SELECT items.itemID
     , itemTitle
     , itemSKULadies
     , itemSKUMen
     , itemDescLadies
     , itemDescMen
     , itemPrice
     , itemColours
     , categories.category
     , suppliers.supplier
     , itemTypes.itemType
     , sizesMen.size AS Msize
     , sizesLadies.size AS Lsize
     , [COLOR="Red"]sc.subcategories[/COLOR]
     , [COLOR="Blue"]ss.siteSections[/COLOR]
  FROM items
LEFT OUTER
  JOIN categories
    ON categories.catID = items.catID
LEFT OUTER
  JOIN suppliers
    ON suppliers.supplierID = items.supplierID
LEFT OUTER
  JOIN itemTypes
    ON itemTypes.itemTypeID = items.itemTypeID
LEFT OUTER
  JOIN sizesMen
    ON sizesMen.sizeMenID = items.sizeMenID
LEFT OUTER
  JOIN sizesLadies
    ON sizesLadies.sizeLadiesID = items.sizeLadiesID
LEFT OUTER
  JOIN [COLOR="Red"]( 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 )[/COLOR] AS sc
    ON sc.itemID = items.itemID
LEFT OUTER
  JOIN [COLOR="Blue"]( 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 )[/COLOR] AS ss
    ON ss.itemID = items.itemID

notice there is no GROUP BY in the outer query

23

Thanks for the extra code. Just tried it and I get the error:

Error fetching items: Unknown column ‘item_to_subcat.itemID’ in ‘group statement’

What does that mean?

Also not sure about the following lines:

sc.subcategories
ss.siteSections

If sc represents a table and sucategories a column then that doesn’t match my database. I have no table called sc.

Can you clarify?

24

then it’s possible that a site has 2 subsections and 3 subcategories?

Just to come back to your above question. What I meant here was that an item could belong to two sections. The site itself has two sections but in the items table an item can be assigned to more than one site section which is why I have the item_to_siteSection many-to-many lookup table.

Not sure if this changes things?

25

it means you did not copy my query correctly :slight_smile:

yes you do, it’s a derived table created by a subquery in the FROM clause

the sc subquery is highlighted in red, and the ss subquery in blue

26

This is my current query:

	$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?

27

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

28

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.