Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    56

    Unanswered: Subquerry inside GROUP_CONCAT

    The following code works and needs to be expanded
    Code:
    SELECT
    		products.productId
    	,	products.productTypeId
    	,	types.moduleName
    	,	products.workflowCode
    	,	GROUP_CONCAT( product_properties.productPropertyId  SEPARATOR ', ')
    	,	products.active
    
    FROM
    	products
    	INNER JOIN
    		product_properties
    	ON
    		product_properties.productId = products.productId
    
    	INNER JOIN
    		types
    	ON
    		types.productTypeId = products.productTypeId
    
    WHERE
    	products.productTypeId = 3
    	AND
    	products.productId = 1
    
    GROUP BY
    	products.productId
    The
    Code:
    GROUP_CONCAT( product_properties.productPropertyId  SEPARATOR ', ')
    returns property Ids than need to be used to lookup the property name on the property_definitions table.
    I tried a few subqueries without success, one example:
    Code:
    SELECT
    		products.productId
    	,	products.productTypeId
    	,	types.moduleName
    	,	products.workflowCode
    	,	GROUP_CONCAT(
    						(
    						SELECT
    								property_definitions.productPropertyName
    						FROM
    							product_properties
    							INNER JOIN
    								property_definitions
    							ON
    								property_definitions.productPropertyId = product_properties.productPropertyId
    						WHERE
    							property_definitions.productPropertyId = ???????
    						)
    						SEPARATOR ', ' 
    					)
    	,	products.active
    
    FROM
    	products
    	INNER JOIN
    		product_properties
    	ON
    		product_properties.productId = products.productId
    
    	INNER JOIN
    		types
    	ON
    		types.productTypeId = products.productTypeId
    
    WHERE
    		products.productTypeId = 3
    	AND
    		products.productId = 1
    GROUP BY
    	products.productId
    How would I write a subquery that returns "productPropertyName" from the "property_definitions" table for each "productPropertyId" in the working code?

    Thanks for any help,
    David

  2. #2
    Join Date
    Jul 2009
    Posts
    56
    Oh man, I was thinking in the wrong direction.
    Here is the code that does the job:
    Code:
    SELECT
    		products.productId
    	,	products.productTypeId
    	,	types.moduleName
    	,	products.workflowCode
    	,	GROUP_CONCAT(
    					property_definitions.productPropertyName
    					SEPARATOR ', ' 
    				)
    	,	products.active
    
    FROM
    	products
    	INNER JOIN
    		product_properties
    	ON
    		product_properties.productId = products.productId
    
    	INNER JOIN
    		types
    	ON
    		types.productTypeId = products.productTypeId
    
    	INNER JOIN
    		property_definitions
    	ON
    		property_definitions.productPropertyId = product_properties.productPropertyId
    
    WHERE
    		products.productTypeId = 3
    	AND
    		products.productId = 1
    GROUP BY
    	products.productId
    Sorry for talking to myself here on the forum, but maybe someone can learn from my mistakes.

Posting Permissions

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