?
Solved

an sql query that needs a dynamic table name

Posted on 2008-10-22
14
Medium Priority
?
357 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
12 Comments
 
LVL 61

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 61

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 61

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 61

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 61

Accepted Solution

by:
Kevin Cross earned 2000 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 61

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

612 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