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

    Unanswered: subquery result as select_expression ?

    I have the following code, that does only half of what I want it to, but executes without error
    Code:
    SELECT
    		properties.productId
    	,	properties.propertyName
    	,	finishes.workflowCode
    FROM
    	finishes
    	INNER JOIN
    		(		
    			SELECT
    					product_properties.productId AS productId
    				,	property_definitions.productPropertyName AS propertyName
    				,	product_properties.productPropertyValue AS propertyValue
    				,	property_definitions.productPropertyLokkupTableName AS tableName
    
    			FROM
    				product_properties
    				INNER JOIN
    					property_definitions
    				ON
    					product_properties.productPropertyId = property_definitions.productPropertyId
    		) AS properties
    	ON
    		finishes.productFinishId = properties.propertyValue
    I want it to do something like this:
    Code:
    SELECT
    		properties.productId
    	,	properties.propertyName
    	,	finishes.workflowCode
    FROM
    	CONCAT( properties.tableName )
    	INNER JOIN
    		(		
    			SELECT
    					product_properties.productId AS productId
    				,	property_definitions.productPropertyName AS propertyName
    				,	product_properties.productPropertyValue AS propertyValue
    				,	property_definitions.productPropertyLokkupTableName AS tableName
    
    			FROM
    				product_properties
    				INNER JOIN
    					property_definitions
    				ON
    					product_properties.productPropertyId = property_definitions.productPropertyId
    		) AS properties
    	ON
    		CONCAT( properties.tableName, properties.propertyName ) = properties.propertyValue
    but of course the CONCAT() does not convert the result from the subquerry, which is executed first and should be available for the main querry, from a string into an expression for the FROM and ON clauses.

    Any idea how to achieve this?

    Thanks,
    David

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think you've over-generalized yourself right into a corner

    why are you storing a table name? why is the table a variable for the query?

    why can't you just store everything that belongs together in one table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    56
    Quote Originally Posted by r937 View Post
    i think you've over-generalized yourself right into a corner
    You are right if the corner has no way out
    Quote Originally Posted by r937 View Post
    why are you storing a table name? why is the table a variable for the query?

    why can't you just store everything that belongs together in one table?
    because I have different product types each with a different, mostly overlapping set of properties. Some properties are predefined, hence the stored table name, and some will read a direct value from a table higher up in the tree.
    I need this level of generalization to allow for future product types with a yet unknown set of properties.

    Thanks for your reply,
    David

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you'll probably want to run the subquery by itself, and then tailor a second query based on that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    56
    I tried that, but did not find a way of letting the following query loop over the results of the first.
    The biggest problem so far is writing a variable query syntax.
    FROM appears not to accept an @tableName variable etc.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    FROM appears not to accept an @tableName variable etc.
    yup, and a prepared statement EXECUTE cannot use a placeholder for a table name, only data values

    you'll have to have an "if" block and a separate query for every table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2009
    Posts
    56
    I tried to solve the problem by generalizing even more and put all properties for all product types into a single table.
    That would have been possible, but I stopped when I saw that the data itself would be completely unreadable and very hard to maintain event through SQL / PHP.
    So I generalized less, like you suggested and created a property table for every product type and it works like a charm.
    Now with every new product-type, a new properties table has to be defined for the new product. That is OK and less work than generalizing the whole system.

    Thanks a lot,
    David

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
  •