Solved

an sql query that needs a dynamic table name

Posted on 2008-10-22
14
331 Views
Last Modified: 2013-12-07
hello,
i hv a query like this ----select * from (select table_name from tbllinker where rank=5) where name='tudop'. select table_name from tbllinker where rank=5-->this query will return table name based on rank provided and based on this table name i will fetch data.but this query is not running in mysql.any ideas?
thnx
0
Comment
Question by:mithunda5011
  • 6
  • 2
  • 2
  • +1
14 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22779934
In SQL Server, I would build a dynamic query like this:

DECLARE @table_name nvarchar(50), @sql nvarchar(100)

SELECT @table_name = table_name
FROM tbllinker WHERE rank = 5

SET @sql = 'SELECT * FROM [' + @table_name + '] WHERE name = ''tudop'''
EXEC(@sql)

I will see if I can find the equivalent for MySQL, but figured that would help give you the jist of what to do.
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22779981
I don't think you can use a dynamic table name is a sql statement,

you may use dynamic sql statement for the whole string
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22779982
Alright, I know this should work:

$table_name = "yourtable";
SELECT * FROM $table_name;

However, give this a try to incorporate your query.
SELECT $table_name = table_name
FROM tbllinker WHERE rank = 5;
 
SELECT * FROM $table_name WHERE name='tudop';

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 9

Expert Comment

by:jamesgu
ID: 22779991
agree with mwvisa1
0
 
LVL 5

Expert Comment

by:JBart_17
ID: 22779993
your subselect does not return a field named "name" only a field named "table_name"
Select *
from
     (Select table_name
       from tbllinker
       where rank = 5)
where table_name = 'tudop'
0
 
LVL 5

Expert Comment

by:JBart_17
ID: 22780018
wow, I'm slow by the time I posted, 4 other post magically appeared :-)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22780040
Which is what my example is - Dynamic SQL.

Here is probably a better example of how to construct and execute dynamic SQL query in MySQL:
http://datacharmer.blogspot.com/2005/10/power-of-dynamic-queries.html
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22780060
Think my first example only works from PHP.  The blog post seems to be more fitting as Jamesqu pointed out, this is not possible from normal SQL statement.
0
 

Author Comment

by:mithunda5011
ID: 22780351
so far the very fisrt post looks nearest ...but i will give it a try on mysql :)
0
 

Author Comment

by:mithunda5011
ID: 22780396
i think i need to create a stored proc with 2 input params--one is the rank and other is the name...and then try something like mwvisa1' first post...can anybody give me that store proc...in mysql?
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22780947
http:#22780040

See the link in the above post.  The author has an example of a stored procedure that calls dynamic query.  The syntax is:

set @query = 'dynamic query statement';
prepare table_list from @query;
execute table_list;
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 23338343
Thought my first post and http:#22780040 answered the question.  mithunda5011, confirmed that I thought.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question