SQL construct query from result

Question

Given a table rules

rule_id = 1
rule_condition = 'size > 15'

how can I run a query like this

SELECT * FROM rawdata where (Select rule_condition FROM rules WHERE rule_id=1)

Is the only way to do it a stored procedure like this: mysql - query inside a query

What records are you wanting? The exmple just returns the same result as the nested query.. — CarpeNoctumDC
– CarpeNoctumDC, Commented Mar 9, 2011 at 15:00
Your query has table rawdata, which isn't joining to the subquery. Try adding a join condition, i.e. where rawdata.column = (select rule_condition...) — Shan Plourde
– Shan Plourde, Commented Mar 9, 2011 at 15:03
Do you want the outer SQL SELECT * FROM rawdata to be executed with a fully dynamic WHERE clause? In your example you want the query to return the results of SELECT * FROM rawdata WHERE Size > 15 ? — Simen S
– Simen S, Commented Mar 9, 2011 at 15:06
Lets backup for a sec.. Essentially you want a select statment to return a record set for rule_id = 1 and rule_condition = 'size > 15' correct? What type of column is rule_condition (what type of data is stored there) — CarpeNoctumDC
– CarpeNoctumDC, Commented Mar 9, 2011 at 15:14
@Carpe: in the rawdata table there would be records like size = 10 and size = 17 of these I want only the ones matching the condition, so in — Cilvic
– Cilvic, Commented Mar 9, 2011 at 15:15

Martin Smith · Accepted Answer · 2011-03-09 15:44:13Z

2

This is essential reading for you before starting with dynamic SQL

For SQL Server you would use something like...

DECLARE @dynamicsql NVARCHAR(1000)

SELECT @dynamicsql =N'SELECT * FROM rawdata where ' + rule_condition 
FROM rules 
WHERE rule_id=1

IF(@@ROWCOUNT = 1)
   EXEC(@dynamicsql)

Hopefully you are not planning on allowing users to enter arbitrary rule_condition s as they could execute any arbitrary statements against your SQL Server.

answered Mar 9, 2011 at 15:44

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cilvic Over a year ago

Thanks, I will try as soon as I have access to my server, the rule_condition won't be public

msarchet · Accepted Answer · 2011-03-09 15:17:11Z

2

declare whereClause VarChar(128);
BEGIN 

Select rule_condition into whereClause FROM rules WHERE rule_id=1
SET @s = CONCAT('SELECT * FROM rawdata where ', whereClause;) 
PREPARE stmt1 FROM @s; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1; 

END

This should get you close.

Note this will only work if the first select that selects a string into whereClause is a single record.

answered Mar 9, 2011 at 15:17

msarchet

15.3k2 gold badges45 silver badges66 bronze badges

Collectives™ on Stack Overflow

SQL construct query from result

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related