how to turn result rows from JOIN to result columns ?

vivoices · #1 (**permalink**) 04-21-10, 12:34

My database has different product types that need flexibility for product properties, so the properties are stored in a product_properties table.
I need to retrieve a list of products for each product_type, so the JOIN below naturally returns two result rows for each product, because product type 3 has a set of 2 properties.

Code:

SELECT
		products.productId
	,	products.productTypeId
	,	types.moduleName
	,	products.workflowCode
	,	product_properties.productPropertyId
	,	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

How do I need to change my querry-text to receive one row for each product that has a column for each property to hold the properties' values?

Thanks for any help,
David

r937 · #2 (**permalink**) 04-21-10, 14:30

and different product types other than type 3 have different properties other than 2?

my advice: do the reformatting in your front-end application language

use the database for efficient storage and retrieval, not re-arranging data into layouts that you find more pleasing

vivoices · #3 (**permalink**) 04-21-10, 15:04

Thanks for your advice, Rudy.
Here is the code that does what I intended:

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

but this is just one of several levels of nesting and lookups for one product.
So the question is shifted to performance.
MySQL is good at db queries and should perform better (faster, less memory) than a series of queries from php and "gluing" the data-sets together in php, or not?

[I bought your book and will give you feedback when I start reading]

David

r937 · #4 (**permalink**) 04-21-10, 16:27

Quote:

Originally Posted by vivoices

MySQL is good at db queries and should perform better (faster, less memory) than a series of queries from php and "gluing" the data-sets together in php, or not?

in general, yes, very much better

Internet Services

Web Development

Digital Marketing

Consumer Tech