subquery result as select_expression ?

vivoices · #1 (**permalink**) 04-22-10, 10:46

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

r937 · #2 (**permalink**) 04-22-10, 11:01

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?

vivoices · #3 (**permalink**) 04-22-10, 11:16

Quote:

Originally Posted by r937

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

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

r937 · #4 (**permalink**) 04-22-10, 11:23

you'll probably want to run the subquery by itself, and then tailor a second query based on that

vivoices · #5 (**permalink**) 04-22-10, 11:29

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.

r937 · #6 (**permalink**) 04-22-10, 14:38

Quote:

Originally Posted by vivoices

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

vivoices · #7 (**permalink**) 04-23-10, 14:06

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

Internet Services

Web Development

Digital Marketing

Consumer Tech