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
dBforums
>
Subquerry inside GROUP_CONCAT
04-22-10, 06:10
Linear Mode
