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

    Unanswered: how to turn result rows from JOIN to result columns ?

    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
    Last edited by vivoices; 04-21-10 at 13:37.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    56
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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